SQL：如何处理NULL和PARTITION BY？

Question

我有一个问题，如果你不介意的话。 假设我这里有这样一个表格 – 产品（2000 年按季度销售的金额，只有同一产品和季度（具有不同日期）有多个条目）：

产品	季度	销售金额
牛仔裤	1	20
牛仔裤	2	40
牛仔裤	3	60
牛仔裤	4	5
裙子	1	10
裙子	2	5
裙子	3	30
衬衫	1	15
衬衫	2	40
衬衫	3	60
Blouse	4	15

一下，如下：

产品	quarter1quarter2quarter3quarter4Jeans	不同	5	的
重新	20	40	60	5Skirt
40	10	30	介绍	Null
Blouse	15	60	我想	15

我决定用分区来做（因为它不那么简单，有 相同产品的同一季度的行，但销售量不同，这就是为什么sum(amount_sold)，但我希望你明白了）：

WITH quater_sales as(
SELECT DISTINCT pro.product, pro.quarter, to_char (sum(pro.amount_sold) OVER (PARTITION BY pro.product, pro.quarter)) AS quater
FROM products pro
ORDER BY pro.pro.product)
SELECT quater_sales.prod_product, quater_sales.quater AS "Q1", qu2.quater AS "Q2", qu3.quater AS "Q3", qu4.quater AS "Q4"
FROM quater_sales
 JOIN quater_sales qu2 ON quater_sales.prod_subcategory=qu2.prod_subcategory
 JOIN quater_sales qu3 ON quater_sales.prod_subcategory=qu3.prod_subcategory
 JOIN quater_sales qu4 ON quater_sales.prod_subcategory=qu4.prod_subcategory
WHERE quater_sales.calendar_quarter_number=1 and qu2.calendar_quarter_number=2 and qu3.calendar_quarter_number=3 and qu4.calendar_quarter_number=4

问题在于分区（或者可能是选择的条件），即在所有 4 个季度中未售出的产品被丢弃。我最终得到的基本上是这样的：

我	如何	15	出现	在
60	让	“	裙子	”
也	那么	产品quarter1quarter2quarter3quarter4Jeans 20 40 60 5 Blouse 15 40	那里	呢

？我有点坚持这个。

Answer 1

您是否考虑过使用 PIVOT 语句？

WITH
    quarter_sales (product, quarter, amount_sold)
    AS
        (SELECT 'Jeans', 1, 20 FROM DUAL
         UNION ALL
         SELECT 'Jeans', 2, 40 FROM DUAL
         UNION ALL
         SELECT 'Jeans', 3, 60 FROM DUAL
         UNION ALL
         SELECT 'Jeans', 4, 5 FROM DUAL
         UNION ALL
         SELECT 'Skirt', 1, 10 FROM DUAL
         UNION ALL
         SELECT 'Skirt', 2, 5 FROM DUAL
         UNION ALL
         SELECT 'Skirt', 3, 30 FROM DUAL
         UNION ALL
         SELECT 'Blouse', 1, 15 FROM DUAL
         UNION ALL
         SELECT 'Blouse', 2, 40 FROM DUAL
         UNION ALL
         SELECT 'Blouse', 3, 60 FROM DUAL
         UNION ALL
         SELECT 'Blouse', 4, 15 FROM DUAL)
SELECT *
  FROM (SELECT *
          FROM quarter_sales qs)
       PIVOT (SUM (amount_sold)
             FOR quarter
             IN (1 AS quarter1, 2 AS quarter2, 3 AS quarter3, 4 AS quarter4));


   PRODUCT    QUARTER1    QUARTER2    QUARTER3    QUARTER4
__________ ___________ ___________ ___________ ___________
Blouse              15          40          60          15
Jeans               20          40          60           5
Skirt               10           5          30

Answer 2

尝试枢轴。这就是您在 tsql

declare @tmp as table(product varchar(20),quarter int,[amount sold] int);

insert into @tmp values
('Jeans',   1,  20)
,('Jeans',  2,  40)
,('Jeans',  3,  60)
,('Jeans',  4,  5)
,('Skirt',  1,  10)
,('Skirt',  2,  5)
,('Skirt',  3,  30)
,('Blouse', 1,  15)
,('Blouse', 2,  40)
,('Blouse', 3,  60)
,('Blouse', 4,  15)


    select  product, [1] as quarter1,[2] as quarter2,[3] as quarter3,[4] as quarter4
from
(
select product,quarter,[amount sold] from @tmp)p
pivot
(
sum([amount sold])
for quarter in([1],[2],[3],[4])
) as pvt

输出中进行透视的方式：

product quarter1    quarter2    quarter3    quarter4
Blouse  15          40          60          15
Jeans   20          40          60          5
Skirt   10          5           30          NULL