主页 / dba / 问题 / 219763
Accepted
James
Asked: 2018-10-11 06:21:55 +0800 CST

获得前 N 的最佳方法是什么，然后是 n + 1

  • 772

例如，如果我有这个查询：

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

我可以很容易地统计出最受欢迎的十个药方。

我可以像这样用 Union 添加“其他所有内容”：

    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)

但这把我的桌子打了三遍！它也很难支持，因为相同的 where 子句必须在子查询中。下一个开发人员很容易更改一个而不是另一个。

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

问题：什么是获得前 N 项的最佳方法，然后是其他所有内容？

样本数据（神圣的空白！我有一些规范化要做！）：

    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 个回答

  1. Best Answer

    这对你有用吗？

    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. 使用 Mathewb 关于 case 语句和 row_number() 函数的建议，我已经像这样重写了查询。它只打到桌子上一次：

        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

相关问题