PostgreSQL - 涉及多列时无法使用 PARTITION BY

Question

假设我有一个包含 5 列的表格:

• 年份
• 公司
• 部门
• 员工
• 销售

而我想查询结果得到如下结果:

• 公司
• 部门
• 员工
• 总和(销售额)

额外的要求是首先显示具有最大销售额的公司(所有部门)，然后公司内具有最大销售额的部门首先显示公司，对于员工列也是如此。

成功:当我仅查询 COMPANY 时，以下查询工作正常:

 SELECT company,
       SUM(sales)
FROM   company_sales
GROUP  BY company
ORDER  BY SUM(SUM(sales))
            over (
              PARTITION BY company) DESC,
          company;

成功:当我按公司和部门分组时，以下查询工作正常:

SELECT company,
       department,
       SUM(sales)
FROM   company_sales
GROUP  BY company,
          department
ORDER  BY SUM(SUM(sales))
            over (
              PARTITION BY company) DESC,
          SUM(SUM(sales))
            over (
              PARTITION BY department) DESC,
          company,
          department;

失败:当我按公司、部门和员工分组时，以下查询失败:

SELECT company,
       department,
       employee,
       SUM(sales)
FROM   company_sales
GROUP  BY company,
          department,
          employee
ORDER  BY SUM(SUM(sales))
            over (
              PARTITION BY company) DESC,
          SUM(SUM(sales))
            over (
              PARTITION BY department) DESC,
          SUM(SUM(sales))
            over (
              PARTITION BY employee) DESC,
          company,
          department,
          employee;

这是上面的查询数据供您使用:

WITH COMPANY_SALES AS (
SELECT  2010    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-1' as employee,  100 as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-2' as employee,  101 as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-3' as employee,  102 as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-1' as employee,  50  as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-2' as employee,  51  as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-3' as employee,  52  as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-4' as employee,  53  as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-5' as employee,  54  as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-1' as employee,  80  as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-2' as employee,  81  as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-3' as employee,  82  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-1' as employee,  20  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-2' as employee,  21  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-3' as employee,  22  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-1' as employee,  90  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-2' as employee,  91  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-3' as employee,  92  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-4' as employee,  93  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-5' as employee,  94  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-6' as employee,  95  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-C' as department, 'Employee-B-C-1' as employee,  40  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-1' as employee,  60  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-2' as employee,  61  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-3' as employee,  62  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-4' as employee,  63  as sales
UNION SELECT    2010    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-1' as employee,  20  as sales
UNION SELECT    2010    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-2' as employee,  21  as sales
UNION SELECT    2010    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-3' as employee,  22  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-1' as employee,  30  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-2' as employee,  29  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-3' as employee,  28  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-4' as employee,  27  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-1' as employee,  45  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-2' as employee,  25  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-3' as employee,  78  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-4' as employee,  23  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-1' as employee,  12  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-2' as employee,  54  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-3' as employee,  79  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-1' as employee,  34  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-2' as employee,  89  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-3' as employee,  60  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-1' as employee,  23  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-2' as employee,  62  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-3' as employee,  45  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-4' as employee,  78  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-5' as employee,  45  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-6' as employee,  9   as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-C' as department, 'Employee-B-C-1' as employee,  73  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-1' as employee,  93  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-2' as employee,  27  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-3' as employee,  59  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-4' as employee,  73  as sales
UNION SELECT    2011    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-1' as employee,  54  as sales
UNION SELECT    2011    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-2' as employee,  67  as sales
UNION SELECT    2011    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-3' as employee,  84  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-1' as employee,  78  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-2' as employee,  69  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-3' as employee,  45  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-4' as employee,  69  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-1' as employee,  24  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-2' as employee,  35  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-3' as employee,  89  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-4' as employee,  64  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-5' as employee,  75  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-1' as employee,  15  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-2' as employee,  38  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-1' as employee,  46  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-2' as employee,  85  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-3' as employee,  63  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-1' as employee,  24  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-2' as employee,  59  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-3' as employee,  76  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-4' as employee,  18  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-5' as employee,  94  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-6' as employee,  55  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-C' as department, 'Employee-B-C-1' as employee,  69  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-1' as employee,  37  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-2' as employee,  84  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-3' as employee,  52  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-4' as employee,  55  as sales
UNION SELECT    2012    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-1' as employee,  78  as sales
UNION SELECT    2012    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-2' as employee,  94  as sales
UNION SELECT    2012    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-3' as employee,  21  as sales
)
SELECT company,
       department,
       employee,
       SUM(sales)
FROM company_sales
GROUP  BY company,
          department,
          employee
ORDER  BY SUM(SUM(sales))
            over (
              PARTITION BY company) DESC,
          SUM(SUM(sales))
            over (
              PARTITION BY department) DESC,
          SUM(SUM(sales))
            over (
              PARTITION BY employee) DESC,
          company,
          department,
          employee;

Answer 1

在您的示例数据集中，部门是唯一的，因此您可以:

PARTITION BY department

但是，如果不止一家公司可以拥有同一个部门，您需要:

PARTITION BY company, department

---

旁注:由于您是根据company、department、employee 进行分组，所以第三个over 子句是多余的。您可以通过省略它来简化:

ORDER  BY SUM(SUM(sales)) over (PARTITION BY company) DESC
,         SUM(SUM(sales)) over (PARTITION BY company, department) DESC
,         SUM(sales) DESC