具有字段中值的联接、分组和计数的 SQL View

Question

目标是最终生成一个 View ，显示每种类型和专业组合的 50 个最常见的程序代码。 (专业代码可以在不同类型中重复)

我有两个表:

表 providers 包含:
供应商类型
provider_specialty
供应商编号
(以及一些对这个问题无关紧要的数据)

表服务包含:
供应商编号
程序代码
(和其他一些数据点 - 不相关)

最终结果看起来像这样:

Provider Type  |  Provider Specialty |  Procedure Code  |  Count
---------------+---------------------+------------------+-------
00             |  01                 | 97012            |  500 
00             |  01                 | 97013            |  450
00             |  02                 | 97012            |  270
00             |  02                 | 97013            |  900
01             |  01                 | 97012            |  845
01             |  01                 | 97013            |  1200

有什么想法吗？我知道这是一个 super 复杂的查询，我考虑过只构建一个脚本来执行更新周期，但我宁愿有更多的实时数据。

Answer 1

the 50 most common procedure codes for the combination of each type and specialty

应该像这样工作:

WITH x AS (
    SELECT p.provider_type, p.provider_specialty, s.procedure_code
          ,count(*) AS ct
          ,row_number() OVER (PARTITION BY p.provider_type, p.provider_specialty
                              ORDER BY count(*) DESC) AS r
    FROM   providers p
    JOIN   services s USING (provider_id)
    GROUP  BY 1,2,3
    )
SELECT x.provider_type, x.provider_specialty, x.procedure_code, x.ct
FROM   x
WHERE  x.r <= 50
ORDER  BY 1,2,4,3;

要点

• 我使用 CTE(PostgreSQL 8.4 或更高版本)，您可以对子查询执行相同的操作。

• 我使用 window function row_number() (同样，PostgreSQL 8.4 或更高版本)，以限定 procedure_code 中的 50 个最常见的值。

• 请注意，您可以在 OVER 子句中使用 ORDER BY count(*)。也就是说，在窗口函数中使用聚合函数的结果，因为窗口函数在聚合函数之后应用。

• 您可以不对 ORDER BY ct 执行相同的操作，因为窗口函数不知道别名。

• 最后的 SELECT 很简单，只需返回每个组的前 50 名即可。