时间戳

首页 > 解决方案 > 在sql中创建自定义周数

mysql - 在sql中创建自定义周数

问题描述

我有一个包含 ID 和日期列的表。

表 A：

ID 日期
24 2019-10-29
24 2019-10-30
24 2019-10-31
...... ……

我需要添加一个名为“Week_Num”的列，以便：

  1. 如果有一个新的“ID”并且第一个日期从星期二开始（例如：ID 24 中的 2019 年 10 月 29 日是星期二），那么该周将从 1 开始
  2. 一周总是在星期六结束，无论 7 天是否结束，只有一个例外（见第 3 点）
  3. 如果有一个新的“ID”并且第一个日期在星期二之前开始（例如：ID 25 中的 2020 年 10 月 25 日是星期日），则该周将从 0 开始，一旦到达星期二，week_num 将变为 1。

预期输出：

ID 日期 Day_Of_Week 周数
24 2019-10-29 周二 1
24 2019-10-30 星期三 1
24 2019-10-31 星期四 1
24 2019-11-01 周五 1
24 2019-11-02 星期六 1
24 2019-11-03 太阳 2
24 2019-11-04 星期一 2
24 2019-11-05 周二 2
24 2019-11-06 星期三 2
24 2019-11-07 星期四 2
24 2019-11-08 周五 2
24 2019-11-09 星期六 2
24 ………… . ..
24 2020-03-14 . ..
25 2020-10-25 太阳 0
25 2020-10-26 星期一 0
25 2020-10-27 周二 1
25 2020-10-28 星期三 1
25 2020-10-29 星期四 1
25 2020-10-30 周五 1
25 2020-10-31 星期六 1

到目前为止我所拥有的：

select 
distinct ID,min(Date) over (partition by ID order by date) as firstTuesdayOfSeason
from
TableA
group by ID,Date
having datepart(weekday,Date)=3

它获取每个新 ID 的第一个星期二并给出以下输出：

ID 第一个星期二
24 2019-10-29
25 2020-10-27

我正在考虑将这个表与表 A（以 ID、日期作为列的表）连接，但我不知道如何实现奇怪的星期六逻辑。

标签： mysqlsql

解决方案

这可能对你有用。

查找每个 ID 的最小日期，偏移到星期二，并针对最小日期使用每行的日期差异进行计算

select a.ID, a.Date, weekday(a.Date), greatest(0, 1 + floor((datediff(a.Date, b.since) - b.off) / 7)) week_num
from TableA a 
join (
  select ID, min(Date) As since, (case when weekday(min(Date)) > 1 then 8 - weekday(min(Date)) else 1 - weekday(min(Date)) end) off
  from TableA
  group by ID
) b ON a.ID = b.ID
order by a.ID, a.Date

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=657c96465338a398ced73bc256ff92c0

推荐阅读