我希望从[日期]列中获取周数,从星期一开始作为一周的第一天。我希望每个星期都能被唯一标识。不是从52到1滚动,而是继续计数到第54周、第55周等。
P.S.我需要这个来正确排序Tableau中的**[Week Date Range]**列。否则,Tableau将按以下方式排序分组数据:
08/08/2022 - 08/14/2022
08/09/2021 - 08/15/2021
08/15/2022 - 08/21/2022
08/16/2021 - 08/22/2021
代码:
CREATE TABLE Dates
(Id INT, Dates DATE);
INSERT INTO Dates
VALUES
(1, '2022-12-19'),
(2, '2022-12-22'),
(3, '2022-12-25'),
(4, '2022-12-26'),
(5, '2022-12-29'),
(6, '2022-12-31'),
(7, '2023-01-01'),
(8, '2023-01-06'),
(9, '2023-01-07'),
(10, '2023-01-09')
SELECT *,
CONCAT(CONVERT(VARCHAR(10), DATEADD(DAY, DATEDIFF(DAY, '19000101', Dates) / 7 * 7, '19000101'), 101), ' - ', CONVERT(VARCHAR(10), DATEADD(DAY, (DATEDIFF(DAY, '19000101', Dates) / 7 + 1) * 7, '18991231'), 101)) AS [Week Date Range (Monday - Sunday)],
DATEPART(ISO_WEEK, Dates) AS [ISO Week Number]
FROM Dates
| 身份证|日期|周日期范围(星期一-星期日)|ISO周数|
| - ------| - ------| - ------| - ------|
| 1个|二〇二二年十二月十九日|2022年12月19日至2022年12月25日|五十一|
| 第二章|二〇二二年十二月二十二日|2022年12月19日至2022年12月25日|五十一|
| 三个|二〇二二年十二月二十五日|2022年12月19日至2022年12月25日|五十一|
| 四个|二〇二二年十二月二十六日|2022年12月26日至2023年1月1日|五十二|
| 五个|二〇二二年十二月二十九日|2022年12月26日至2023年1月1日|五十二|
| 六个|二〇二二年十二月三十一日|2022年12月26日至2023年1月1日|五十二|
| 七|2023年1月1日|2022年12月26日至2023年1月1日|五十二|
| 八个|2023年1月6日|2023年1月2日至2023年1月8日|1个|
| 九|2023年1月7日|2023年1月2日至2023年1月8日|1个|
| 十个|2023年1月9日|2023年1月9日至2023年1月15日|第二章|
2条答案
按热度按时间5f0d552i1#
您的问题并不在于您需要一个连续的周数,而是您要求Tableau排序的"Week Date Range"是一个部分格式为MM/DD/YYYY的字符串,它永远无法跨年正确排序。
最简单的解决方案是提供一个真实的日期值,例如计算出的周开始日期,或者使用
CONVERT(VARCHAR(10), WeekStart, 120)
将该日期作为格式为"yyyy-mm-dd"的字符串。但是,如果确实需要唯一的周数,可以使用相对于"参考星期一"的
DATEDIFF(day, ...) / 7
函数来获得连续整数周数("参考星期一"必须在数据中的所有日期之前,以避免负差异)。以下内容包括两者:
(我还将工作日名称添加到选择列表中作为参考。)
通过将
DATEPART(weekday,...)
与DATEADD(day)
结合使用,还可以简化周开始日期和结束日期的计算。一种可以提高可读性并减少表达式重复的技术是使用
CROSS APPLY
将计算与最终选择列表分开。将上述内容结合起来可能会导致查询重写如下:
上述两个查询都生成以下结果:
| 身份证|日期|工作日|周日期范围(星期一-星期日)|可排序周|可排序周开始|
| - ------| - ------| - ------| - ------| - ------| - ------|
| 1个|二〇二二年十二月十九日|星期一|2022年12月19日至2022年12月25日|小行星6416|二〇二二年十二月十九日|
| 第二章|二〇二二年十二月二十二日|星期四|2022年12月19日至2022年12月25日|小行星6416|二〇二二年十二月十九日|
| 三个|二〇二二年十二月二十五日|星期日|2022年12月19日至2022年12月25日|小行星6416|二〇二二年十二月十九日|
| 四个|二〇二二年十二月二十六日|星期一|2022年12月26日至2023年1月1日|小行星6417|二〇二二年十二月二十六日|
| 五个|二〇二二年十二月二十九日|星期四|2022年12月26日至2023年1月1日|小行星6417|二〇二二年十二月二十六日|
| 六个|二〇二二年十二月三十一日|星期六|2022年12月26日至2023年1月1日|小行星6417|二〇二二年十二月二十六日|
| 七|2023年1月1日|星期日|2022年12月26日至2023年1月1日|小行星6417|二〇二二年十二月二十六日|
| 八个|2023年1月6日|星期五|2023年1月2日至2023年1月8日|小行星6418| 2023年1月2日|
| 九|2023年1月7日|星期六|2023年1月2日至2023年1月8日|小行星6418| 2023年1月2日|
| 十个|2023年1月9日|星期一|2023年1月9日至2023年1月15日|小行星6419| 2023年1月9日|
有关工作示例,请参见this db<>fiddle。
nnt7mjpx2#
我认为你基本上走在正确的道路上。逻辑如下
您已经完成了上面的前两个步骤;要获得第三个值,我将Tableau的"分组值"设置为一个int值,计算公式为
100 * YEAR(date) + (week number)
-这意味着在上面的数据中,分组值的范围为202251到202302。请注意,这需要在"WeekStart"字段而不是"Dates"字段上完成,因为20230101将返回2023年而不是2022年。
db<>fiddle with answer above here.
结果
我认为这有点复杂,但这里有一个方法来做到这一点:
结果:
上面的Fiddle已经用第二种方法进行了调整。
您可以随意添加varchar "日期范围"字段;这个答案仅仅是与密钥数据一起工作。