How to Get Results Close to a Specific

0

I'm trying to get results close to a specific record. I tried to use ABS, but I could not do it using it. The result limit is 5 .

Data

MARCA   NOME_MARCA          MEDIA
8       TOYOTA              100
11      HONDA               100
5       FORD                100
10      CHEVROLET           0
6       FIAT                -100
4       VOLKSWAGEN          -100

I have these data above. I would like to make a query that would return the next two and the previous two. For example, the user is FORD. I want you to return the following in a query:

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 it to show like this:

MARCA   NOME_MARCA          MEDIA
5       FORD                100
10      CHEVROLET           0
6       FIAT                -100
4       VOLKSWAGEN          -100

That is, I always want the tag I bring as a parameter, the previous two (if it has) and the next two (if it has).

My SELECT is a bit large, because it's a calculation I make. 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
    
asked by anonymous 26.10.2016 / 16:19

0 answers