MYSQL获取从星期六开始的一个月中的星期，以零开头

Question

我有一个非常复杂的查询，需要帮助。我有一个包含Order_ID、Date和Day的表。

我需要得到一个月中的星期Week_No。该领域有4个关键条件。

1. 一周从星期六开始，每个月初重新开始
2. 结果的总长度应为3个字符，因此应始终有2个前导零，例如001
3. CCD_ 6将始终从CCD_ 7开始，即使不存在通向该条目的特定CCD_
4. 即使在一个月的某一周没有订单，Week_No也始终递增1

以条件3为例。和4.，

如果6月份只有2个订单，2020-06-29和2020-06-11上各有一个订单，则前者和后者的Week_No将分别为002和001

希望我下面的表格足够清晰。

╔══════════╦════════════╦═════╦═════════╗
║ Order_ID ║    Date    ║ Day ║ Week_No ║
╠══════════╬════════════╬═════╬═════════╣
║       11 ║ 2020-06-25 ║ Thu ║     002 ║
║       10 ║ 2020-06-24 ║ Wed ║     002 ║
║        9 ║ 2020-06-20 ║ Sat ║     002 ║
║        8 ║ 2020-06-11 ║ Thu ║     001 ║
║        7 ║ 2020-05-31 ║ Sun ║     006 ║
║        6 ║ 2020-05-31 ║ Sun ║     006 ║
║        5 ║ 2020-05-29 ║ Fri ║     005 ║
║        4 ║ 2020-05-20 ║ Wed ║     004 ║
║        3 ║ 2020-05-14 ║ Thu ║     003 ║
║        2 ║ 2020-05-07 ║ Thu ║     002 ║
║        1 ║ 2020-05-01 ║ Fri ║     001 ║
╚══════════╩════════════╩═════╩═════════╝

Answer 1

嗯。实际上，你可以用lead和一个累加和来做这件事。基本逻辑是：

• 如果上一个日期在同一个月，并且中间有一个星期六，则加1
• 从本月第一个日期的1开始

逻辑如下：

select t.*,
sum(case when extract(year_month from date) <> extract(year_month from prev_date) or prev_date is null
then 1
when datediff(date, prev_date) >= 7
then 1
when day = 'Fri' or
day = 'Thu' and prev_day not in ('Fri') or
day = 'Wed' and prev_day not in ('Fri', 'Thu') or
day = 'Tue' and prev_day not in ('Fri', 'Thu', 'Wed') or
day = 'Mon' and prev_day not in ('Fri', 'Thu', 'Wed', 'Tue') or
day = 'Sun' and prev_day not in ('Fri', 'Thu', 'Wed', 'Tue', 'Mon') 
then 0
else 1
end) over (order by date) as week_num
from (select t.*,
lag(date) over (order by date) as prev_date,
lag(day) over (order by day) as prev_day
from t
) t