使用多列时无法使用带计数(*)的分区

Question

假设我有一个具有下列列的表：

• 类型
• 部门
• 供货商
• 订单
• 合计

我想查询这些数据，以便得到有序的结果，首先按类型分组。命令是订单的数量。然后，以下查询 for me (http://sqlfiddle.com/#!15/78cc1/1)运行良好：

WITH company_sales(type, department, supplier, order_number, total) AS (
   VALUES
     ('Edibles'    , 'Department-1', 'Supplier-1' , 'ORDER-1' ,   10)
   , ('Edibles'    , 'Department-1', 'Supplier-2' , 'ORDER-2' ,   20)
   , ('Edibles'    , 'Department-1', 'Supplier-3' , 'ORDER-3' ,   30)
   , ('Edibles'    , 'Department-1', 'Supplier-4' , 'ORDER-4' ,   40)
   , ('Edibles'    , 'Department-2', 'Supplier-5' , 'ORDER-5' ,   50)
   , ('Edibles'    , 'Department-2', 'Supplier-6' , 'ORDER-6' ,   60)
   , ('Edibles'    , 'Department-3', 'Supplier-7' , 'ORDER-7' ,   70)
   , ('Edibles'    , 'Department-3', 'Supplier-8' , 'ORDER-8' ,   80)
   , ('Edibles'    , 'Department-3', 'Supplier-9' , 'ORDER-9' ,   90)
   , ('Edibles'    , 'Department-3', 'Supplier-9' , 'ORDER-10',  100)
   , ('Edibles'    , 'Department-4', 'Supplier-10', 'ORDER-11',  110)
   , ('Non-Edibles', 'Department-2', 'Supplier-11', 'ORDER-12', 1000)
   , ('Non-Edibles', 'Department-3', 'Supplier-12', 'ORDER-13', 1010)
   , ('Non-Edibles', 'Department-3', 'Supplier-13', 'ORDER-14', 1020)
   , ('Non-Edibles', 'Department-3', 'Supplier-14', 'ORDER-15', 1030)
   , ('Non-Edibles', 'Department-3', 'Supplier-14', 'ORDER-16', 1040)
   , ('Non-Edibles', 'Department-4', 'Supplier-15', 'ORDER-17', 1050)
)
SELECT cs.type,
       count(*)   sum_total_count,
       sum(total) sum_grand_total
FROM   company_sales cs
GROUP  BY cs.type
ORDER  BY Sum(Count(*)) OVER (partition BY cs.type) DESC,
          cs.type ASC;

如果我想查询这些数据，以便得到有序的结果，首先按类型分组，然后按部门分组。命令是订单的数量。然后，以下查询 for me (http://sqlfiddle.com/#!15/78cc1/2)运行良好：

WITH company_sales(type, department, supplier, order_number, total) AS ( ...)
SELECT cs.type,
       cs.department,
       count(*)   sum_total_count,
       sum(total) sum_grand_total
FROM   company_sales cs
GROUP  BY cs.type,
          cs.department
ORDER  BY Sum(Count(*)) OVER (partition BY cs.type) DESC,
          Sum(Count(*)) OVER (partition BY cs.type, cs.department) DESC,
          cs.type ASC,
          cs.department ASC;

但是，当我想要订购结果时，遵循相同的模式，首先按类型分组，然后按部门分组，然后按供应商分组，订单是订单的数量。然后，下面的查询对我来说不工作(http://sqlfiddle.com/#!15/78cc1/3)：

WITH company_sales(type, department, supplier, order_number, total) AS (...)
SELECT cs.type,
       cs.department,
       cs.supplier,
       count(*)   sum_total_count,
       sum(total) sum_grand_total
FROM   company_sales cs
GROUP  BY cs.type,
          cs.department,
          cs.supplier
ORDER  BY Sum(Count(*)) OVER (partition BY cs.type) DESC,
          Sum(Count(*)) OVER (partition BY cs.type, cs.department) DESC,
          Sum(Count(*)) OVER (partition BY cs.type, cs.department, cs.supplier) DESC,
          cs.type ASC,
          cs.department ASC,
          cs.supplier ASC;

以上查询的结果如下：

​

​

鉴于此，我希望以下几点：

​

​

我哪里出问题了？

Answer 1

两项初步工作：

1. 从不使用为列名保留关键字。在type -> typ和order -> ordnum之后。
2. 学习使用VALUES子句。我建议您按照下面的示例编辑您的问题，不需要重复3次(只需使用类似于WITH company_sales (...) AS (...) SELECT ...的内容，例如，PG手册就是这样做的)。事实上，你的问题过于冗长，人们不会仔细阅读。(好的- Erwin Brandstetter从OP中编辑了这篇文章)

First

查询根据所需的顺序返回结果，因为您指定的ORDER BY子句顺序错误。在typ = 'Edibles'下，1部门和3部门都有4个订单。通过分析您的ORDER BY子句，我们可以看到以下内容：

      -- 1. This orders by the type with the most orders - OK
ORDER BY Sum(Count(*)) OVER (PARTITION BY cs.type) DESC,
      -- 2. Number of orders by department within each type - OK but tie #1 and #3
         Sum(Count(*)) OVER (PARTITION BY cs.type, cs.department) DESC,
      -- 3. Number of orders by supplier with each type, department - NOT OK
         Sum(Count(*)) OVER (PARTITION BY cs.type, cs.department, cs.supplier) DESC,
      -- 4. In case of ties, order by type name - OK
         cs.type ASC,
      -- 5. In case of ties, order by department name - OK
         cs.department ASC,
      -- 6. In case of ties, order by supplier name - OK
         cs.supplier ASC;

那为什么3号不行呢？实际上，#3很好，但是#4、#5和#6的位置不对。您希望按类型的数量，然后是部门，然后是供应商，并按字母顺序解决关系。在每次对行数进行排序后(删除过时的表别名和ASC子句)，您应该立即解决这些联系：

ORDER BY
  Sum(Count(*)) OVER (PARTITION BY typ) DESC, typ,
  Sum(Count(*)) OVER (PARTITION BY typ, department) DESC, department,
  Sum(Count(*)) OVER (PARTITION BY typ, department, supplier) DESC, supplier;

那你为什么点错了？那么，“可供食用”中的第一和第三部门各有四行，因此它们被绑在一起。以下条款订购供应商9，比其他供应商高出2份，各订购1份。按部门名称订购之前，供应商订单打破部门之间的联系，您的所有行订单都很好。

然后

Sum(Count(*)) OVER (PARTITION BY ...)条款是无稽之谈。count(*)为分区中的每一行分配一个新列，其中包含该分区中的行数。再加一次，就会得到与基本行数完全相同的排序属性的正方形。

此外，Sum(Count(*)) OVER (PARTITION BY typ, department, supplier)是无用的，因为您已经在选择列表中GROUP BY这些列以生成count(*)。

给出

将所有这些组合在一起，并使用位置参数来简化，然后我们得到：

WITH company_sales(typ, department, supplier, ordnum, total) AS (...)
SELECT typ,
       department,
       supplier,
       count(*) sum_total_count,
       sum(total) sum_grand_total
FROM   company_sales
GROUP  BY 1, 2, 3
ORDER BY
      count(*) OVER (PARTITION BY typ) DESC, 1,
      count(*) OVER (PARTITION BY typ, department) DESC, 2,
      4 DESC, 3;

其结果是：

     typ     |  department  |  supplier   | sum_total_count | sum_grand_total
-------------+--------------+-------------+-----------------+-----------------
 Edibles     | Department-1 | Supplier-1  |               1 |              10
 Edibles     | Department-1 | Supplier-2  |               1 |              20
 Edibles     | Department-1 | Supplier-3  |               1 |              30
 Edibles     | Department-1 | Supplier-4  |               1 |              40
 Edibles     | Department-3 | Supplier-9  |               2 |             190
 Edibles     | Department-3 | Supplier-7  |               1 |              70
 Edibles     | Department-3 | Supplier-8  |               1 |              80
 Edibles     | Department-2 | Supplier-5  |               1 |              50
 Edibles     | Department-2 | Supplier-6  |               1 |              60
 Edibles     | Department-4 | Supplier-10 |               1 |             110
 Non-Edibles | Department-3 | Supplier-12 |               1 |            1010
 Non-Edibles | Department-3 | Supplier-13 |               1 |            1020
 Non-Edibles | Department-3 | Supplier-14 |               2 |            2070
 Non-Edibles | Department-2 | Supplier-11 |               1 |            1000
 Non-Edibles | Department-4 | Supplier-15 |               1 |            1050
(15 rows)