#sql #sql-server #common-table-expression #cumulative-sum
#sql #sql-сервер #common-table-expression #кумулятивная сумма
Вопрос:
Я пытаюсь создать запрос, который содержит текущее количество недель ISO, когда у учетной записи нет записи в таблице [Volume]. Запрос должен возвращать только выборку учетных записей, поэтому я создал пару CTE, чтобы ограничить количество записей и объединить таблицу объемов, чтобы в результатах отображались недели без объема.
Чтобы проиллюстрировать результат, который я ищу:
ISOWk | SurrID | Weekly Volume | No vol Count
201601 | 001 | 0 | 1
201601 | 002 | 5 | 0
201602 | 001 | 0 | 2
201602 | 002 | 0 | 1
201603 | 001 | 125| 0
201603 | 002 | 75 | 0
201604 | 001 | 0 | 1
201604 | 002 | 75 | 0
Как вы можете видеть, учетная запись с SurrID 001 не имеет объема в течение недель 201601 и 201602, поэтому [No Volume Count] равна 2 на неделе 201602. На неделе 201603 есть объем, поэтому счетчик сбрасывается на 0 и увеличивается до 1 недели 201604.
Из проведенного мной исследования мне удалось запустить последовательный подсчет, используя ROW_NUMBER с помощью функции window, но он не сбрасывается при наличии объема (как на неделе 201603 в моем примере). Чего я не могу понять, так это как подсчитывать последовательные нулевые значения и сбрасывать их при необходимости.
Я включил свой полный запрос ниже, чтобы вы получили полную картину (пожалуйста, укажите здесь на какие-либо особенно плохие практики — я все еще нахожу свой путь!). Все работает по назначению, пока я не включу третий CTE «NDs». Затем требуется 45 минут, чтобы вернуть чуть более 2000 строк и возвращает количество без сброса для строк, не показывающих еженедельный объем.
WITH Surrs AS
(
SELECT SurrID, OracleStartDate AS OSD
FROM (
SELECT ca.SurrID, OracleStartDate, ROW_NUMBER() OVER(ORDER BY OracleStartDate) as rn
FROM tblCustomerAccounts ca
JOIN tblAccountUpdates au
ON ca.SurrID = au.SurrID
WHERE CustomerType_ID IN (1,2,3,4,5,6,7,12)
AND au.ISOWk = 201641
) a
WHERE rn % 1000 = 0
),
Updates AS
(
SELECT au.ISOWk, s.SurrID, (CASE WHEN AccStatus_ID = 1 THEN 'A' ELSE 'I' END) AS AccStatus, (CASE WHEN dbo.udf_ConvertDateToISOWeek(OSD) <= BBC THEN 'B' ELSE 'F' END) AS Book
FROM Surrs s
JOIN (
SELECT ISOWk,
(SELECT BBCutOff FROM dbo.udf_CutOffWeeks(ISOWk)) AS BBC,
(SELECT FYStart FROM dbo.udf_CutOffWeeks(ISOWk)) AS FYS,
(SELECT FYEnd FROM dbo.udf_CutOffWeeks(ISOWk)) AS FYE,
(SELECT BYStart FROM dbo.udf_CutOffWeeks(ISOWk)) AS BYS,
(SELECT BYEnd FROM dbo.udf_CutOffWeeks(ISOWk)) AS BYE,
SurrID,
AccStatus_ID
FROM tblAccountUpdates
) au
ON au.SurrID = s.SurrID
),
NDs AS
(
SELECT u.ISOWk, u.SurrID, ROW_NUMBER() OVER (PARTITION BY u.SurrID ORDER BY u.ISOWk) AS NDCount
FROM Updates u
LEFT JOIN tblTotalVolumes tv
ON u.SurrID = tv.SurrID
AND u.ISOWk = tv.ISOWk
WHERE tv.Volume IS NULL
AND u.ISOWk >= 201601
)
SELECT tw.ISOWk,
tw.SurrID,
(CASE WHEN Volume IS NULL THEN 0 ELSE Volume END) AS [Weekly Volume],
tw.Book,
tw.AccStatus,
(CASE WHEN tw.AccStatus = 'I' AND lw.AccStatus = 'A' THEN 'Y' ELSE '' END) AS [Stopped this week],
(CASE WHEN tw.AccStatus = 'A' AND lw.AccStatus = 'I' THEN 'Y' ELSE '' END) AS [Restarted this week],
(CASE WHEN NDCount IS NULL THEN 0 ELSE NDCount END) AS [Consecutive ND Weeks]
FROM Updates tw
JOIN Updates lw
ON lw.ISOWk = dbo.udf_ConvertDateToISOWeek(DATEADD("ww",-1,dbo.udf_ConvertISOWkToDate(tw.ISOWk)))
AND tw.SurrID = lw.SurrID
LEFT JOIN tblTotalVolumes tv
ON tw.SurrID = tv.SurrID
AND tw.ISOWk = tv.ISOWk
LEFT JOIN NDs
ON tw.SurrID = nds.SurrID
AND tw.ISOWk = nds.ISOWk
ORDER BY tw.ISOWk
Чтобы повторить, что мне нужно: столбец [Последовательные ND недель] должен подсчитывать последовательные недели, где [Еженедельный объем] равен 0. Помощь будет высоко оценена.
Спасибо
Обновить:
Я пытался реализовать сообщение @Gordon Linoff, но мой счетчик не сбрасывается на 0, когда есть значение для [Еженедельного объема]. Вот мой исправленный запрос:
SELECT t.*, (CASE WHEN [Weekly Volume] = 0 THEN ROW_NUMBER() OVER (PARTITION BY t.SurrID, grp ORDER BY ISOWk) ELSE 0 END) AS [ND Count]
FROM (
SELECT tw.ISOWk,
s.SurrID,
tw.AccStatus,
(CASE WHEN tv.Volume IS NULL THEN 0 ELSE tv.Volume END) AS [Weekly Volume],
(CASE WHEN dbo.udf_ConvertDateToISOWeek(OSD) <= BBC THEN 'B' ELSE 'F' END) AS Book,
(CASE WHEN tw.AccStatus = 'I' AND lw.AccStatus = 'A' THEN 'Y' ELSE '' END) AS [Stopped this week],
(CASE WHEN tw.AccStatus = 'A' AND lw.AccStatus = 'I' THEN 'Y' ELSE '' END) AS [Restarted this week],
SUM(CASE WHEN tv.volume > 0 THEN 1 ELSE 0 END) OVER(PARTITION BY tv.SurrID ORDER BY tv.ISOWk) AS grp
FROM (
SELECT SurrID, OracleStartDate AS OSD
FROM (
SELECT ca.SurrID, OracleStartDate, ROW_NUMBER() OVER(ORDER BY OracleStartDate) as rn
FROM tblCustomerAccounts ca
JOIN tblAccountUpdates au
ON ca.SurrID = au.SurrID
WHERE CustomerType_ID IN (1,2,3,4,5,6,7,12)
AND au.ISOWk = 201641
) a
WHERE rn % 1000 = 0
) s
JOIN (
SELECT ISOWk,
(SELECT BBCutOff FROM dbo.udf_CutOffWeeks(ISOWk)) AS BBC,
(SELECT FYStart FROM dbo.udf_CutOffWeeks(ISOWk)) AS FYS,
(SELECT FYEnd FROM dbo.udf_CutOffWeeks(ISOWk)) AS FYE,
(SELECT BYStart FROM dbo.udf_CutOffWeeks(ISOWk)) AS BYS,
(SELECT BYEnd FROM dbo.udf_CutOffWeeks(ISOWk)) AS BYE,
SurrID,
(CASE WHEN AccStatus_ID = 1 THEN 'A' ELSE 'I' END) AS AccStatus
FROM tblAccountUpdates
) tw
ON tw.SurrID = s.SurrID
JOIN (
SELECT ISOWk,
SurrID,
(CASE WHEN AccStatus_ID = 1 THEN 'A' ELSE 'I' END) AS AccStatus
FROM tblAccountUpdates
) lw
ON tw.SurrID = lw.SurrID
AND dbo.udf_ConvertDateToISOWeek(DATEADD("ww",-1,dbo.udf_ConvertISOWkToDate(tw.ISOWk))) = lw.ISOWk
LEFT JOIN tblTotalVolumes tv
ON tw.ISOWk = tv.ISOWk
AND tw.SurrID = tv.SurrID
) t
ORDER BY ISOWk
Обновить:
Теперь я изменил свой запрос, чтобы отразить решение Владимира (еще раз, это полный запрос):
SELECT ISOWk,
SurrID,
AccStatus,
[Weekly Volume],
Book,
[Stopped this week],
[Restarted this week],
RN1,
RN2,
grp,
rn3,
(CASE WHEN [Weekly Volume] = 0 THEN rn3 ELSE 0 END) AS [ND Count]
FROM (
SELECT t.ISOWk,
t.SurrID,
t.AccStatus,
t.[Weekly Volume],
t.Book,
t.[Stopped this week],
t.[Restarted this week],
rn1,
rn2,
rn1 - rn2 AS grp,
ROW_NUMBER() OVER(PARTITION BY t.SurrID, rn1-rn2 ORDER BY ISOWk) AS rn3
FROM (
SELECT tw.ISOWk,
s.SurrID,
tw.AccStatus,
(CASE WHEN tv.Volume IS NULL THEN 0 ELSE tv.Volume END) AS [Weekly Volume],
(CASE WHEN dbo.udf_ConvertDateToISOWeek(OSD) <= BBC THEN 'B' ELSE 'F' END) AS Book,
(CASE WHEN tw.AccStatus = 'I' AND lw.AccStatus = 'A' THEN 'Y' ELSE '' END) AS [Stopped this week],
(CASE WHEN tw.AccStatus = 'A' AND lw.AccStatus = 'I' THEN 'Y' ELSE '' END) AS [Restarted this week],
ROW_NUMBER() OVER(PARTITION BY tw.SurrID ORDER BY tw.ISOWk) AS rn1,
ROW_NUMBER() OVER(PARTITION BY tw.SurrID, tv.Volume ORDER BY tw.ISOWk) AS rn2
FROM (
SELECT SurrID, OracleStartDate AS OSD
FROM (
SELECT ca.SurrID, OracleStartDate, ROW_NUMBER() OVER(ORDER BY OracleStartDate) as rn
FROM tblCustomerAccounts ca
JOIN tblAccountUpdates au
ON ca.SurrID = au.SurrID
WHERE CustomerType_ID IN (1,2,3,4,5,6,7,12)
AND au.ISOWk = 201641
) a
WHERE rn % 2000 = 0
) s
JOIN (
SELECT ISOWk,
(SELECT BBCutOff FROM dbo.udf_CutOffWeeks(ISOWk)) AS BBC,
(SELECT FYStart FROM dbo.udf_CutOffWeeks(ISOWk)) AS FYS,
(SELECT FYEnd FROM dbo.udf_CutOffWeeks(ISOWk)) AS FYE,
(SELECT BYStart FROM dbo.udf_CutOffWeeks(ISOWk)) AS BYS,
(SELECT BYEnd FROM dbo.udf_CutOffWeeks(ISOWk)) AS BYE,
SurrID,
(CASE WHEN AccStatus_ID = 1 THEN 'A' ELSE 'I' END) AS AccStatus
FROM tblAccountUpdates
) tw
ON tw.SurrID = s.SurrID
JOIN (
SELECT ISOWk,
SurrID,
(CASE WHEN AccStatus_ID = 1 THEN 'A' ELSE 'I' END) AS AccStatus
FROM tblAccountUpdates
) lw
ON tw.SurrID = lw.SurrID
AND dbo.udf_ConvertDateToISOWeek(DATEADD("ww",-1,dbo.udf_ConvertISOWkToDate(tw.ISOWk))) = lw.ISOWk
LEFT JOIN tblTotalVolumes tv
ON tw.ISOWk = tv.ISOWk
AND tw.SurrID = tv.SurrID
) t
) x
ORDER BY ISOWk
Вот пример неожиданных (в том смысле, что счетчик не сбрасывается на 0, когда [Еженедельный объем] имеет значение больше 0, обозначенное звездочками) результатов. Все следующие имеют одинаковый идентификатор, поэтому я удалил столбец ID.
ISOWk | Weekly Volume | rn1 | rn2 | grp | rn3 | ND Count |
201620 | 0 | 1 | 1 | 0 | 1 | 1 |
201621 | 0 | 2 | 2 | 0 | 2 | 2 |
201622 | 0 | 3 | 3 | 0 | 3 | 3 |
201623 | 0 | 4 | 4 | 0 | 4 | 4 |
201624 | 0 | 5 | 5 | 0 | 5 | 5 |
201625 | 53 | 6 | 1 | 5 | 1 | 0 |
201626 | 49 | 7 | 1 | 6 | 1 | 0 |
201627 | 98 | 8 | 1 | 7 | 1 | 0 |
201628 | 54 | 9 | 1 | 8 | 1 | 0 |
201629 | 53 | 10 | 2 | 8 | 2 | 0 |
201630 | 103 | 11 | 1 | 10 | 1 | 0 |
201631 | 59 | 12 | 1 | 11 | 1 | 0 |
201632 | 35 | 13 | 1 | 12 | 1 | 0 |
201633 | 0 | 14 | 6 | 8 | 3 | 3 |**
201634 | 0 | 15 | 7 | 8 | 4 | 4 |**
201635 | 0 | 16 | 8 | 8 | 5 | 5 |**
201636 | 0 | 17 | 9 | 8 | 6 | 6 |**
201637 | 87 | 18 | 1 | 17 | 1 | 0 |
201638 | 136 | 19 | 1 | 18 | 1 | 0 |
201639 | 56 | 20 | 1 | 19 | 1 | 0 |
201640 | 70 | 21 | 1 | 20 | 0 | 0 |
201641 | 77 | 22 | 1 | 21 | 1 | 0 |
В моем наборе данных есть другие случаи этой проблемы.
Ответ №1:
Это похоже на gaps-and-islands проблему.
Пример данных
DECLARE @T TABLE(ISOWk int, SurrID char(3), WeeklyVolume int);
INSERT INTO @T(ISOWk, SurrID, WeeklyVolume) VALUES
(201601, '001', 0),
(201601, '002', 5),
(201602, '001', 0),
(201602, '002', 0),
(201603, '001',125),
(201603, '002', 75),
(201604, '001', 0),
(201604, '002', 75),
(201620, '003', 0),
(201621, '003', 0),
(201622, '003', 0),
(201623, '003', 0),
(201624, '003', 0),
(201625, '003', 53),
(201626, '003', 49),
(201627, '003', 98),
(201628, '003', 54),
(201629, '003', 53),
(201630, '003',103),
(201631, '003', 59),
(201632, '003', 35),
(201633, '003', 0),
(201634, '003', 0),
(201635, '003', 0),
(201636, '003', 0),
(201637, '003', 87),
(201638, '003',136),
(201639, '003', 56),
(201640, '003', 70),
(201641, '003', 77),
(201601, '004', 0),
(201602, '004', 6),
(201603, '004', 0),
(201604, '004', 0);
Я добавил ваш расширенный образец с помощью SurrID=003 и добавил свой образец с помощью SurrID=004 .
Запрос
WITH
CTE
AS
(
SELECT
ISOWk
,SurrID
,WeeklyVolume
,ROW_NUMBER() OVER (PARTITION BY SurrID ORDER BY ISOWk) AS rn1
,ROW_NUMBER() OVER (PARTITION BY SurrID,WeeklyVolume ORDER BY ISOWk) AS rn2
FROM @T
)
,CTE2
AS
(
SELECT
ISOWk
,SurrID
,WeeklyVolume
,rn1
,rn2
,rn1-rn2 AS grp
,ROW_NUMBER() OVER (PARTITION BY SurrID,WeeklyVolume,rn1-rn2 ORDER BY ISOWk) AS rn3
FROM CTE
)
SELECT
ISOWk
,SurrID
,WeeklyVolume
,rn1
,rn2
,grp
,rn3
,CASE WHEN WeeklyVolume = 0 THEN rn3 ELSE 0 END AS NoVolumeCount
FROM CTE2
ORDER BY SurrID, ISOWk;
Результат
-------- -------- -------------- ----- ----- ----- ----- ---------------
| ISOWk | SurrID | WeeklyVolume | rn1 | rn2 | grp | rn3 | NoVolumeCount |
-------- -------- -------------- ----- ----- ----- ----- ---------------
| 201601 | 001 | 0 | 1 | 1 | 0 | 1 | 1 |
| 201602 | 001 | 0 | 2 | 2 | 0 | 2 | 2 |
| 201603 | 001 | 125 | 3 | 1 | 2 | 1 | 0 |
| 201604 | 001 | 0 | 4 | 3 | 1 | 1 | 1 |
| 201601 | 002 | 5 | 1 | 1 | 0 | 1 | 0 |
| 201602 | 002 | 0 | 2 | 1 | 1 | 1 | 1 |
| 201603 | 002 | 75 | 3 | 1 | 2 | 1 | 0 |
| 201604 | 002 | 75 | 4 | 2 | 2 | 2 | 0 |
| 201620 | 003 | 0 | 1 | 1 | 0 | 1 | 1 |
| 201621 | 003 | 0 | 2 | 2 | 0 | 2 | 2 |
| 201622 | 003 | 0 | 3 | 3 | 0 | 3 | 3 |
| 201623 | 003 | 0 | 4 | 4 | 0 | 4 | 4 |
| 201624 | 003 | 0 | 5 | 5 | 0 | 5 | 5 |
| 201625 | 003 | 53 | 6 | 1 | 5 | 1 | 0 |
| 201626 | 003 | 49 | 7 | 1 | 6 | 1 | 0 |
| 201627 | 003 | 98 | 8 | 1 | 7 | 1 | 0 |
| 201628 | 003 | 54 | 9 | 1 | 8 | 1 | 0 |
| 201629 | 003 | 53 | 10 | 2 | 8 | 1 | 0 |
| 201630 | 003 | 103 | 11 | 1 | 10 | 1 | 0 |
| 201631 | 003 | 59 | 12 | 1 | 11 | 1 | 0 |
| 201632 | 003 | 35 | 13 | 1 | 12 | 1 | 0 |
| 201633 | 003 | 0 | 14 | 6 | 8 | 1 | 1 |
| 201634 | 003 | 0 | 15 | 7 | 8 | 2 | 2 |
| 201635 | 003 | 0 | 16 | 8 | 8 | 3 | 3 |
| 201636 | 003 | 0 | 17 | 9 | 8 | 4 | 4 |
| 201637 | 003 | 87 | 18 | 1 | 17 | 1 | 0 |
| 201638 | 003 | 136 | 19 | 1 | 18 | 1 | 0 |
| 201639 | 003 | 56 | 20 | 1 | 19 | 1 | 0 |
| 201640 | 003 | 70 | 21 | 1 | 20 | 1 | 0 |
| 201641 | 003 | 77 | 22 | 1 | 21 | 1 | 0 |
| 201601 | 004 | 0 | 1 | 1 | 0 | 1 | 1 |
| 201602 | 004 | 6 | 2 | 1 | 1 | 1 | 0 |
| 201603 | 004 | 0 | 3 | 2 | 1 | 1 | 1 |
| 201604 | 004 | 0 | 4 | 3 | 1 | 2 | 2 |
-------- -------- -------------- ----- ----- ----- ----- ---------------
Я включил промежуточные столбцы в результат, чтобы вы могли понять, как это работает.
Стандартный подход для gaps-and-islands — это две последовательности ROW_NUMBER : одна является простой последовательностью ( rn1 ), вторая разделена на WeeklyVolume ( rn2 ).
Разница между rn1 и rn2 дает идентификатор группы (острова) ( grp ). Вычислите другую последовательность номеров строк, разделенных на WeeklyVolume и эту группу ( rn3 ), и используйте ее только тогда, когда WeeklyVolume равно нулю.
Очевидно, что все, что указано выше, сначала разбивается на SurrID .
В первом варианте ответа я забыл включить WeeklyVolume в последнее разбиение для rn3 .
Комментарии:
1. Это работает в основном так, как ожидалось. Однако, похоже, существуют некоторые комбинации значений, которые приводят к одному и тому же значению для
grp, несмотря на то, чтоWeekly Volumeзначения не совпадают; таким образом, в результате получается ошибочноеNoVolCountзначение. Возможно ли это, или моя реализация неверна (в контексте вопроса)?2. @Andrewwwilliams, пожалуйста, расширьте пример данных, покажите нам эти специальные комбинации и покажите нам, какого результата вы ожидаете.
3. Идеальный. Это дает мне результат, который я ожидал. Ваше предложение было очень полезным из-за предоставленных вами примеров данных, и я могу четко видеть, как это работает. Спасибо!
Ответ №2:
Вот подход:
- Подсчитайте количество предшествующих значений, отличных от 0 для
SurrId. Это число определяет группы. - Выполните
row_number()внутри каждой группы. - Учитывайте только
row_number()когда значение равно0.
Это приводит к:
select t.*,
(case when weeklyvolume = 0
then row_number() over (partition by SurrId, grp order by ISOwk)
else 0
end) as NoVolCount
from (select t.*,
sum(case when weeklyvolume > 0 then 1 else 0 end) over (partition by SurrId order by ISOwk) as grp
from t
) t;
Комментарии:
1. Я внес изменения в свой запрос, чтобы я мог использовать ваш формат, но
[NoVolCount]не сбрасывается, когда строка имеет еженедельный объем, если, конечно, я не внедрил ваше решение неправильно. Я отредактировал свой пост и был бы признателен, если бы вы взглянули.