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 (
  SELECT
    EXTRACT(DOW FROM order_date) + 1 AS DayOfWeek, -- 调整为周一为1，周日为7
    i.item_category,
    SUM(o.quantity) AS TotalQuantity
  FROM Orders o
  LEFT JOIN Items i ON o.item_id = i.item_id
  GROUP BY DayOfWeek, i.item_category
)
SELECT
  item_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
;