0
I’m trying to get results close to a specific record. I tried to use the ABS, but I couldn’t do it using it. The result limit is 5.
Dice
MARCA NOME_MARCA MEDIA
8 TOYOTA 100
11 HONDA 100
5 FORD 100
10 CHEVROLET 0
6 FIAT -100
4 VOLKSWAGEN -100
I have this data above. I would like to make a query that returns me the next two and the previous two. For example, the user is FORD. I want you to call me back in a consult on the following:
MARCA NOME_MARCA MEDIA
8 TOYOTA 100
11 HONDA 100
5 FORD 100
10 CHEVROLET 0
6 FIAT -100
FORD is in the middle by coincidence. But if it’s FIAT, I want you to show it like this:
MARCA NOME_MARCA MEDIA
5 FORD 100
10 CHEVROLET 0
6 FIAT -100
4 VOLKSWAGEN -100
That is, I want to always appear the tag I take as parameter, the previous two (if you have) and the next two (if you have).
My SELECT is a little big, because it’s a calculation I do. But I’ll post below:
SELECT
`empresas`.`id_marca` AS `id_marca`,
`brands`.`marca` AS `marca`,
sum(`pro`.`qtd_pro`) AS `qtd_pro`,
sum(`det`.`qtd_det`) AS `qtd_det`,
sum(`neu`.`qtd_neu`) AS `qtd_neu`,
round(
(
(
(
sum(
COALESCE (`pro`.`qtd_pro`, 0)
) - sum(
COALESCE (`det`.`qtd_det`, 0)
)
) / (
(
sum(
COALESCE (`pro`.`qtd_pro`, 0)
) + sum(
COALESCE (`neu`.`qtd_neu`, 0)
)
) + sum(
COALESCE (`det`.`qtd_det`, 0)
)
)
) * 100
),
0
) AS `nps`
FROM
(
(
(
(
`empresas`
LEFT JOIN (
SELECT
`empresa_ratings`.`id_empresa` AS `id_empresa`,
COALESCE (
count(
`empresa_ratings`.`rating_nps`
),
0
) AS `qtd_pro`
FROM
`empresa_ratings`
WHERE
(
`empresa_ratings`.`rating_nps` >= 9
)
GROUP BY
`empresa_ratings`.`id_empresa`
) `pro` ON (
(
`pro`.`id_empresa` = `empresas`.`id`
)
)
)
LEFT JOIN (
SELECT
`empresa_ratings`.`id_empresa` AS `id_empresa`,
COALESCE (
count(
`empresa_ratings`.`rating_nps`
),
0
) AS `qtd_det`
FROM
`empresa_ratings`
WHERE
(
`empresa_ratings`.`rating_nps` <= 6
)
GROUP BY
`empresa_ratings`.`id_empresa`
) `det` ON (
(
`det`.`id_empresa` = `empresas`.`id`
)
)
)
LEFT JOIN (
SELECT
`empresa_ratings`.`id_empresa` AS `id_empresa`,
COALESCE (
count(
`empresa_ratings`.`rating_nps`
),
0
) AS `qtd_neu`
FROM
`empresa_ratings`
WHERE
(
`empresa_ratings`.`rating_nps` BETWEEN 7
AND 8
)
GROUP BY
`empresa_ratings`.`id_empresa`
) `neu` ON (
(
`neu`.`id_empresa` = `empresas`.`id`
)
)
)
JOIN `brands` ON (
(
`brands`.`id` = `empresas`.`id_marca`
)
)
)
WHERE
(
(`pro`.`qtd_pro` IS NOT NULL)
OR (`det`.`qtd_det` IS NOT NULL)
OR (`neu`.`qtd_neu` IS NOT NULL)
)
GROUP BY
`empresas`.`id_marca`
ORDER BY
nps desc
I think you could have an index and you select the items whose indexes are >= Item Searched Item Searched Minus 2 and <= Item Searched Item Searched Plus 2
– Reginaldo Rigo
@Reginaldorigo But I would have to do 3 Selects or only 1 ?
– Diego Souza
Only one if when mounting the main select you already know the index of the searched item. If not two. Or a main one with a sub select.
– Reginaldo Rigo
I can’t put into code what you’re talking about...
– Diego Souza
How not? Let’s say your table has the indexes, and the FORD Index is 5. If you know beforehand then it would look like this: SELECT BRAND, NOME_MARCA, MEDIA FROM TABLE WHERE INDEX >= ( 5 - 2 ) AND INDICE <= ( 5 + 2 ) or so SELECT BRAND, NOME_MARCA, MEDIA FROM TABLE WHERE INDICE >= ( SELECT INDICE - 2 WHERE MARCA = 'FORD' ) AND INDICE <= ( SELECT INDICE + 2 WHERE MARCA = 'FORD' )
– Reginaldo Rigo
I’m dumb. This index you refer to is the unique registration ID or is it something else ?
– Diego Souza
Yes, it could be, or someone else whose business rule puts them next to each other.
– Reginaldo Rigo
It can’t be the ID because the ID can jump.
– Diego Souza
Put another one. And put the next ones following a logical rule so that they appear in the same select.
– Reginaldo Rigo