leetcode 1479 周内每天的销售情况(postgresql)

需求

表：Orders

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| order_id | int |
| customer_id | int |
| order_date | date |
| item_id | varchar |
| quantity | int |
±--------------±--------+
(order_id, item_id) 是该表主键
该表包含了订单信息
order_date 是id为 item_id 的商品被id为 customer_id 的消费者订购的日期.
表：Items

±--------------------±--------+
| Column Name | Type |
±--------------------±--------+
| item_id | varchar |
| item_name | varchar |
| item_category | varchar |
±--------------------±--------+
item_id 是该表主键
item_name 是商品的名字
item_category 是商品的类别

你是企业主，想要获得分类商品和周内每天的销售报告。

写一个SQL语句，报告 周内每天 每个商品类别下订购了多少单位。

返回结果表单 按商品类别排序 。

查询结果格式如下例所示。

示例 1：

输入：
Orders 表：
±-----------±-------------±------------±-------------±------------+
| order_id | customer_id | order_date | item_id | quantity |
±-----------±-------------±------------±-------------±------------+
| 1 | 1 | 2020-06-01 | 1 | 10 |
| 2 | 1 | 2020-06-08 | 2 | 10 |
| 3 | 2 | 2020-06-02 | 1 | 5 |
| 4 | 3 | 2020-06-03 | 3 | 5 |
| 5 | 4 | 2020-06-04 | 4 | 1 |
| 6 | 4 | 2020-06-05 | 5 | 5 |
| 7 | 5 | 2020-06-05 | 1 | 10 |
| 8 | 5 | 2020-06-14 | 4 | 5 |
| 9 | 5 | 2020-06-21 | 3 | 5 |
±-----------±-------------±------------±-------------±------------+

Items 表：
±-----------±---------------±--------------+
| item_id | item_name | item_category |
±-----------±---------------±--------------+
| 1 | LC Alg. Book | Book |
| 2 | LC DB. Book | Book |
| 3 | LC SmarthPhone | Phone |
| 4 | LC Phone 2020 | Phone |
| 5 | LC SmartGlass | Glasses |
| 6 | LC T-Shirt XL | T-Shirt |
±-----------±---------------±--------------+
输出：
±-----------±----------±----------±----------±----------±----------±----------±----------+
| Category | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
±-----------±----------±----------±----------±----------±----------±----------±----------+
| Book | 20 | 5 | 0 | 0 | 10 | 0 | 0 |
| Glasses | 0 | 0 | 0 | 0 | 5 | 0 | 0 |
| Phone | 0 | 0 | 5 | 1 | 0 | 0 | 10 |
| T-Shirt | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
±-----------±----------±----------±----------±----------±----------±----------±----------+
解释：
在周一(2020-06-01, 2020-06-08)，Book分类(ids: 1, 2)下，总共销售了20个单位(10 + 10)
在周二(2020-06-02)，Book分类(ids: 1, 2)下，总共销售了5个单位
在周三(2020-06-03)，Phone分类(ids: 3, 4)下，总共销售了5个单位
在周四(2020-06-04)，Phone分类(ids: 3, 4)下，总共销售了1个单位
在周五(2020-06-05)，Book分类(ids: 1, 2)下，总共销售了10个单位，Glasses分类(ids: 5)下，总共销售了5个单位
在周六, 没有商品销售
在周天(2020-06-14, 2020-06-21)，Phone分类(ids: 3, 4)下，总共销售了10个单位(5 + 5)
没有销售 T-Shirt 类别的商品

输入

分析

– - EXTRACT(DOW FROM your_date_column)
– - EXTRACT 是提取函数，它允许你从一个日期/时间类型的值中获取具体的子段（如年、月、日、时、分、秒或星期几）。
– - DOW 是一个指定提取星期几的代码。在 PostgreSQL 中，DOW 的取值范围是 0 到 6，其中：
– - 0 表示星期日，
– - 1 表示星期一，
– - 2 表示星期二，
– - …，
– - 6 表示星期六。

– to_char() 是 PostgreSQL 中的一个函数，用于将日期/时间值转换为特定格式的字符串。这对于美化输出、生成特定格式的日期字符串（例如，用于报告或国际化显示）非常有用。
– - to_char(your_date_column, 'Day')
– - to_char 是转换函数，它接受两个参数：一个是日期/时间表达式，另一个是格式模型字符串，用来定义输出的格式。
– - your_date_column 同样需要替换为你的表中实际的日期时间列名，这是你要转换的日期时间值。
– - 'Day' 是格式模型字符串中的一个特定代码，指定了你希望日期如何被格式化。在这个上下文中，'Day' 会将日期转换为星期的全名，如 ‘Monday’, ‘Tuesday’, ‘Wednesday’ 等。注意这里的 ‘Day’ 是大小写敏感的，并且需要包含在单引号中，它是 PostgreSQL 日期/时间格式化中预定义的模板之一。
– 综上所述，to_char(your_date_column, 'Day') 的作用是将 your_date_column 中的日期时间值转换成星期的全名文本，例如将一个日期转换为 “Monday”、“Tuesday” 等，提供了一种更易读的方式来表示星期几。

– CREATE EXTENSION IF NOT EXISTS tablefunc;

– SELECT *
– FROM crosstab(
– ‘SELECT category, subcategory, value
– FROM your_table
– ORDER BY 1,2’
– ) AS ct (
– category text,
– “subcategory_1” numeric, – 这里的子类别列名需要手动指定，根据你的实际子类别值来
– “subcategory_2” numeric,
– – 添加更多子类别列，根据你的数据中实际出现的子类别数量来确定
– );

输出

with t1 as (
select o.order_id,o.customer_id,to_char(order_date,'Day') as date1,o.item_id,o.quantity,i.item_category,i.item_name
from orders o
left join items i on o.item_id = i.item_id
),t2 as (
select date1,item_category,sum(quantity) as total_sum
from t1
group by date1,item_category
)
select *
from t2;

WITH WeeklySales AS (SELECTEXTRACT(DOW FROM order_date) + 1 AS DayOfWeek, -- 调整为周一为1，周日为7i.item_category,SUM(o.quantity) AS TotalQuantityFROM Orders oLEFT JOIN Items i ON o.item_id = i.item_idGROUP BY DayOfWeek, i.item_category
)
SELECTitem_category AS Category,MAX(CASE WHEN DayOfWeek = 1 THEN TotalQuantity ELSE 0 END) AS Monday,MAX(CASE WHEN DayOfWeek = 2 THEN TotalQuantity ELSE 0 END) AS Tuesday,MAX(CASE WHEN DayOfWeek = 3 THEN TotalQuantity ELSE 0 END) AS Wednesday,MAX(CASE WHEN DayOfWeek = 4 THEN TotalQuantity ELSE 0 END) AS Thursday,MAX(CASE WHEN DayOfWeek = 5 THEN TotalQuantity ELSE 0 END) AS Friday,MAX(CASE WHEN DayOfWeek = 6 THEN TotalQuantity ELSE 0 END) AS Saturday,MAX(CASE WHEN DayOfWeek = 7 THEN TotalQuantity ELSE 0 END) AS Sunday
FROM WeeklySales
GROUP BY item_category
ORDER BY item_category;

with t1 as (
select o.order_id,o.customer_id,extract(dow from order_date) as date1,o.item_id,o.quantity,i.item_category,i.item_name
from orders o
left join items i on i.item_id=o.item_id
),t2 as (
select date1,item_category,sum(quantity) as total_sum
from t1
group by date1,item_category
),t3 as (
select item_category as category1,max(case when date1=1 then total_sum else 0 end) as Monday,max(case when date1=2 then total_sum else 0 end) as Tuesday,max(case when date1=3 then total_sum else 0 end) as Wednesday,max(case when date1=4 then total_sum else 0 end) as Thursday,max(case when date1=5 then total_sum else 0 end) as Friday,max(case when date1=6 then total_sum else 0 end) as Saturday,max(case when date1=0 then total_sum else 0 end) as Sunday
from t2
group by item_category
),t4 as (
select distinct item_category
from items
)
select item_category as category,coalesce(Monday,0) as Monday,coalesce(Tuesday,0) as Tuesday,coalesce(Wednesday,0) as Wednesday,coalesce(Thursday,0) as Thursday,coalesce(Friday,0) as Friday,coalesce(Saturday,0) as Saturday,coalesce(Sunday,0) as Sunday
from t4
left join t3
on t4.item_category=t3.category1
order by item_category
;