Início / dba / Perguntas / 219763
Accepted
James
Asked: 2018-10-11 06:21:55 +0800 CST

Qual é a melhor maneira de obter o top N, então todo o resto como n + 1

  • 772

Por exemplo, se eu tiver esta consulta:

    select top 10 medication_name, count(1)
    from DimMedical
    group by medication_name
    order by count(1) desc

Posso facilmente obter uma contagem das dez prescrições mais populares.

Eu posso adicionar 'tudo o resto' com uma União assim:

    select top 10 medication_name, count(1)
    from DimMedical
    group by medication_name
    order by count(1) desc
    union
    select 'other', count(1)
    from DimMedical
    where medication_name not in (
                select top 10 medication_name, count(1)
                from DimMedical
                group by medication_name
                order by count(1) desc)

mas isso atinge minha mesa três vezes! Também é difícil de suportar, pois a mesma cláusula where deve estar na subconsulta. Seria fácil para o próximo desenvolvedor mudar um e não o outro.

https://www.brentozar.com/pastetheplan/?id=Bk4IqFo9m

Pergunta: Qual é a melhor maneira de obter o top N, então todo o resto?

Dados de amostra (santo espaço em branco! Tenho algumas normalizações para fazer!):

    create table DimMedical (medication_name varchar(255))

    insert into DimMedical(medication_name)
    values ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('METHOCARBAMOL                      '),
    ('IBUPROFEN                          '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('ZIPSOR                             '),
    ('CELEBREX                           '),
    ('GABAPENTIN                         '),
    ('MELOXICAM                          '),
    ('TRAMADOL HCL                       '),
    ('BACLOFEN                           '),
    ('XODOL                              '),
    ('NAPROXEN                           '),
    ('CELEBREX                           '),
    ('CLONAZEPAM                         '),
    ('OXYCODONE HCL-ACETAMINOPHEN        '),
    ('LYRICA                             '),
    ('LIDODERM                           '),
    ('AMOXICILLIN/CLAVULANATE POTASSIUM  '),
    ('CIPROFLOXACIN                      '),
    ('CLINDAMYCIN HCL                    '),
    ('OXYCODONE HCL-ACETAMINOPHEN        '),
    ('OXYCODONE HCL-ACETAMINOPHEN        '),
    ('LYRICA                             '),
    ('OXYCODONE HCL-ACETAMINOPHEN        '),
    ('CELEBREX                           '),
    ('DICLOFENAC SODIUM                  '),
    ('ACETAMINOPHEN-CODEINE PHOSPHATE    '),
    ('VOLTAREN GEL                       '),
    ('ZIPSOR                             '),
    ('FENTANYL CITRATE                   '),
    ('OPANA ER                           '),
    ('CLARINEX                           '),
    ('NASONEX                            '),
    ('IPRATROPIUM BROMIDE-ALBUTEROL SULFA'),
    ('PROAIR HFA                         '),
    ('ADVAIR DISKUS 250/50               '),
    ('SPIRIVA                            '),
    ('OXYCODONE HCL-ACETAMINOPHEN        '),
    ('IBU                                '),
    ('GABAPENTIN                         '),
    ('NAPROXEN                           '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('TIZANIDINE HCL                     '),
    ('GABAPENTIN                         '),
    ('CYCLOBENZAPRINE HCL                '),
    ('ENDOCET                            '),
    ('ENDOCET                            '),
    ('ZOLPIDEM TARTRATE                  '),
    ('KADIAN                             '),
    ('TRAMADOL HCL                       '),
    ('CYCLOBENZAPRINE HCL                '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('TRAMADOL HCL                       '),
    ('GABAPENTIN                         '),
    ('TRAMADOL HCL                       '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('NAPROXEN                           '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('ZYPREXA                            '),
    ('LEXAPRO                            '),
    ('AMITRIPTYLINE HCL                  '),
    ('CYMBALTA                           '),
    ('MIRTAZAPINE                        '),
    ('LIDODERM                           '),
    ('KADIAN                             '),
    ('MELOXICAM                          '),
    ('CYCLOBENZAPRINE HCL                '),
    ('GABAPENTIN                         '),
    ('TRAZODONE HYDROCHLORIDE            '),
    ('DIAZEPAM                           '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('DEMEROL                            '),
    ('TRAMADOL HCL                       '),
    ('AMITRIPTYLINE HCL                  '),
    ('LITHIUM CARBONATE                  '),
    ('ENDOCET                            '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('Unknown'),
    ('NASONEX                            '),
    ('XOPENEX HFA                        '),
    ('ACETAMINOPHEN-CODEINE PHOSPHATE    '),
    ('MELOXICAM                          '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('OXYCODONE HCL                      '),
    ('OXYCONTIN                          '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('CARISOPRODOL                       '),
    ('OXYCODONE HCL-ACETAMINOPHEN        '),
    ('METHOCARBAMOL                      '),
    ('OXYCONTIN                          '),
    ('AMITRIPTYLINE HCL                  '),
    ('HYDROCODONE BITARTRATE-ACETAMINOPHE'),
    ('HYDROXYZINE PAMOATE                '),
    ('ALPRAZOLAM                         '),
    ('CARISOPRODOL                       '),
    ('CITALOPRAM HYDROBROMIDE            ')
sql-server sql-server-2008

2 respostas

  1. Best Answer

    Como isso funciona para você?

    SELECT  medication_name, SUM(medication_count) medication_count
FROM    (
    SELECT  CASE WHEN CountOrder < 11 THEN medication_name ELSE 'other' END medication_name, medication_count
    FROM    (
        SELECT  medication_name, COUNT(1) medication_count, ROW_NUMBER() OVER (ORDER BY COUNT(1) DESC) CountOrder
        FROM    DimMedical
        GROUP BY medication_name
    ) subquery2
) subquery1
GROUP BY medication_name
    • 3

  2. Usando a sugestão de Mathewb de uma instrução case e uma função row_number(), reescrevi a consulta assim. Ele só atinge a mesa uma vez:

        with ranked_meds as (
    select  medication_name, count(Medication_name) as med_count, rank() over (order by count(Medication_name) desc) as count_rank
    from  dimMedical 
    where category = 'Drugs'
    group by medication_name)

    SELECT case when count_rank < 11 then medication_name else 'other' end as medication_name, sum(med_count) as med_count
    FROM ranked_meds
    group by case when count_rank < 11 then medication_name else 'other' end
    order by sum(med_count) desc
    • 1

relate perguntas