Почему мой запрос по-прежнему возвращает значения для 53-й недели, хотя год состоит из 52 недель?
Моя цель - запросить все недели в течение нескольких лет по григорианскому календарю.
Это будет означать, что:
2020 = 53 недели
2021 = 52 недели
2022 = 52 недели
2023 = 52 недели
Почему следующий запрос возвращает значения для 2021 года — НЕДЕЛЯ 53?
SELECT
*
FROM (
SELECT
YEAR(DateTime) AS Year,
'WEEK ' + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(ISO_WEEK,DateTime)), 2) AS Week,
MAX(Value) - MIN(Value) AS Value
FROM
DummyData.dbo.SampleData
WHERE
TagName LIKE '%ENERGY_SAMPLE_1%' AND
DATEPART(YEAR,DateTime) >= '2020' AND
DATEPART(YEAR,DateTime) <= '2022'
GROUP BY
YEAR(DateTime),
DATEPART(ISO_WEEK,DateTime)
) AS SourceTable
PIVOT (
SUM(Value)
FOR Week IN (
--[WEEK 01], [WEEK 02], [WEEK 03], [WEEK 04], [WEEK 05], [WEEK 06], [WEEK 07],
--[WEEK 08], [WEEK 09], [WEEK 10], [WEEK 11], [WEEK 12], [WEEK 13], [WEEK 14],
--[WEEK 15], [WEEK 16], [WEEK 17], [WEEK 18], [WEEK 19], [WEEK 20], [WEEK 21],
--[WEEK 22], [WEEK 23], [WEEK 24], [WEEK 25], [WEEK 26], [WEEK 27], [WEEK 28],
--[WEEK 29], [WEEK 30], [WEEK 31], [WEEK 32], [WEEK 33], [WEEK 34], [WEEK 35],
--[WEEK 36], [WEEK 37], [WEEK 38], [WEEK 39], [WEEK 40], [WEEK 41], [WEEK 42],
--[WEEK 43], [WEEK 44], [WEEK 45], [WEEK 46], [WEEK 47], [WEEK 48], [WEEK 49],
[WEEK 50], [WEEK 51], [WEEK 52],
[WEEK 53]
)
) AS PivotTable;
В результате чего :
Почему неделя 53 2021 года содержит значение ? в первом добавленном изображении ISO_WEEK возвращает 52 недели в году
Любая помощь будет принята с благодарностью.
Согласно Squirrel, этот диапазон дат — 53-я неделя ISO 2020 года. Вы делаете GROUP BY YEAR(DateTime), но год ISO недели ISO имеет собственное отношение.
Если бы только была функция ISO_YEAR...
@Squirrel Я до сих пор не понимаю, какие изменения мне нужно внести, чтобы получить, поэтому я получаю значения только за каждую неделю по григорианскому календарю. что означало бы отсутствие результата для недели 53 2021 года - я понимаю, что значения WK 53, возвращенные для 2021 года, на самом деле относятся не к концу года, а к оставшимся дням прошлого года. но как правильно исключить эту неделю?
Вам нужно определить, что означает ваш собственный номер недели.
Итак, для дат с 2021-01-01 по 2021-01-03 вы хотите указать год 2021 или год 2020?
01.01.2021–03.01.2021 возвращают ISO_WEEK из 53, потому что они находятся на (ISO) неделе 53 2020 года. В зависимости от того, какое определение недели вы хотите использовать (США - неделя 1 = 1 января или ISO = неделя 1 = первый четверг) первый и последний дни каждого года могут относиться к неделе предыдущего или следующего года. Вы должны решить, какое определение недели вы хотите использовать, а затем рассчитать правильный год, которому принадлежит эта неделя в вашем отчете.
Вы можете получить ISO_YEAR даты, как в этом вопросе stackoverflow.com/questions/26926271/…
Ответы 2
Просто чтобы расширить комментарий Белки.
Просто иллюстрация
Select Date = D
,DOW = DateName(WeekDay,D)
,WeekNr = DatePart(WEEK,D)
From ( Select Top 366 N=-1+Row_Number() Over (Order By (Select NULL))
From master..spt_values n1, master..spt_values n2
) A
Cross Apply ( values ( convert(DATE,dateadd(DAY,N,'2021-01-01')) ) ) B(D)
Полученные результаты
Date DOW WeekNr
2021-01-01 Friday 1
2021-01-02 Saturday 1
2021-01-03 Sunday 2
2021-01-04 Monday 2
2021-01-05 Tuesday 2
...
2021-12-19 Sunday 52
2021-12-20 Monday 52
2021-12-21 Tuesday 52
2021-12-22 Wednesday 52
2021-12-23 Thursday 52
2021-12-24 Friday 52
2021-12-25 Saturday 52
2021-12-26 Sunday 53
2021-12-27 Monday 53
2021-12-28 Tuesday 53
2021-12-29 Wednesday 53
2021-12-30 Thursday 53
2021-12-31 Friday 53
2022-01-01 Saturday 1
Спасибо за ответ, теперь я понимаю проблему. но я не могу реализовать решение о том, как исключить 53-ю неделю на 2021 год, как мне к этому подойти?
@leroyv 52 недели — это год — распространенное заблуждение. Конечно, если вы возьмете 365 дней, разделенных на 7, вы получите целое число 52 ... НО десятичное число равно 52.148857 . Это плюс то, что годы будут начинаться в разные дни недели. Жизнь не черно-белая. Примите серый цвет.
Но если вы говорите о неделях ISO (как, по-видимому, op), то 2021-01-01 приходится не на 1-ю неделю 2021 года, а на 53-ю неделю 2020 года, а 1-я неделя 2021 года начинается 2021-01-04, потому что по состоянию на ISO стандартная первая неделя года - это неделя, которая содержит первый четверг года (или, другими словами, имеет не менее 4 дней, принадлежащих этому году)
Если вы хотите классифицировать даты с 2021-01-01 по 2021-01-03 в соответствии с 2020 годом, вы можете вычислить свой собственный ISO_YEAR, а затем сгруппировать по нему.
SELECT ISO_YEAR = YEAR([Date])
+ case when DAY([Date]) <= 7
and DATEPART(ISO_WEEK, [Date]) >= 52
then -1
else 0
end
демонстрационная ссылка продемонстрировал эффект на ощупь с помощью ISO_YEAR вместо Year([Date])
Для вашего запроса вы можете использовать перекрестное применение, чтобы вычислить его и изменить, чтобы использовать его в предложении GROUP BY.
SELECT
YEAR(i.ISO_YEAR) AS Year,
'WEEK ' + RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(ISO_WEEK,DateTime)), 2) AS Week,
MAX(Value) - MIN(Value) AS Value
FROM
DummyData.dbo.SampleData
CROSS APPLY
(
-- compute ISO_YEAR
SELECT ISO_YEAR = YEAR(d.[Date])
+ case when DAY(d.[Date]) <= 7
and DATEPART(ISO_WEEK, d.[Date]) >= 52
then -1
else 0
end
) i
GROUP BY
YEAR(i.ISO_YEAR),
DATEPART(ISO_WEEK,DateTime)
2021-01-01до2021-01-03iso неделя 53