Распределите строки равномерно по дням
У меня есть таблица, куда я положил, давайте назовем это ручными значениями, которые позже будут использованы в моем коде. Эта таблица выглядит так:
subId | MonthNo | PackagesNumber | Country | EntryMethod | PaidAmount | Version
1 | 201701 | 223 | NO | BCD | 44803 | 2
2 | 201701 | 61 | NO | GHI | 11934 | 2
3 | 201701 | 929 | NO | ABC | 88714 | 2
4 | 201701 | 470 | NO | DEF | 98404 | 2
5 | 201702 | 223 | NO | BCD | 28225 | 2
Все, что мне нужно сделать, это разделить эти значения на отдельные строки на уровне одного пакета. Например, в январе 2017 года в стране NO имеется 223 пакета с EntryMethod BCD, поэтому я хочу 223 отдельных строки. PaidAmount также следует разделить на число PackagesNumber.
Проблема в том, что мне нужно связать дату с каждой записью. Записи должны распространяться равномерно в течение всего месяца. У меня есть измерение Date, которое я могу пересечь с моей таблицей, потянув месяц и год отдельно от MontNo. Например, январь 2017 года, EntryMethod BCD У меня есть пакеты, так что это ~7 пакетов в день.
Это то, что я хочу:
subId | Date | Country | Packages | EntryMethod | PaidAmount | Version
1 | 01.01.2017 | NO | 1 | BCD | 200.910313901345 | 2
2 | 01.01.2017 | NO | 1 | BCD | 200.910313901345 | 2
3 | 01.01.2017 | NO | 1 | BCD | 200.910313901345 | 2
4 | 01.01.2017 | NO | 1 | BCD | 200.910313901345 | 2
5 | 01.01.2017 | NO | 1 | BCD | 200.910313901345 | 2
6 | 01.01.2017 | NO | 1 | BCD | 200.910313901345 | 2
7 | 01.01.2017 | NO | 1 | BCD | 200.910313901345 | 2
8 | 02.01.2017 | NO | 1 | BCD | 200.910313901345 | 2
Бонус: я написал код, который разделяет пакеты на отдельные записи, и в качестве даты указывается первый день каждого месяца.
SELECT
Date =
(
SELECT TOP 1
date
FROM dim_Date dim
WHERE dim.Month = a.Month
AND dim.Year = a.Year
)
, Country
, EntryMethod
, Deliveries = 1
, PaidAmount = NULLIF(PaidAmount, 0) / PackagesNumber
, SubscriptionId = 90000000 + ROW_NUMBER() OVER(ORDER BY n.number)
, Version
FROM
(
SELECT
[Year] = LEFT(MonthNo, 4)
, [Month] = RIGHT(MonthNo, 2)
, Country
, EntryMethod
, PackagesNumber
, PaidAmount
, Version
FROM tgm.rep_PredictionsReport_ManualValues tgm
/*WHERE MonthNo = 201701*/
) a
JOIN master..spt_values n
ON n.type = 'P'
AND n.number < CAST(PackagesNumber AS INT);
РЕДАКТИРОВАТЬ: я добился определенного прогресса. Я использовал функцию NTILE, чтобы разделить строки на группы. Единственное, что изменилось, это выбор даты с верхнего уровня. Теперь это выглядит так:
Date = concat([Year], '-', [Month], '-', case when ntile(31) over(order by n.number) < 10 then '0' + cast(ntile(31) over(order by n.number) as varchar(2)) else cast(ntile(31) over(order by n.number) as varchar(2)) end)
Объяснение: Я создаю поле Дата, используя поля Год и Месяц, и NTILE в течение количества дней в месяце (теперь это статический номер, но позже он будет изменен). Результаты не так хороши, как я ожидал, они создают группы в два раза больше, чем они должны быть (14 вместо 7 строк в каждой дате).
2 ответа
Вы можете сделать это с помощью оператора по модулю, который позволяет разделить элементы на определенное количество категорий.
Вот полный тест: http://rextester.com/TOROA96856
Вот соответствующий запрос:
--recursive query to expand each row.
with expand_rows (subid,monthno,month,packagesnumber,paidamount) as (
select subid,monthno,month,packagesnumber,(paidamount+0.0000)/packagesnumber
from initial_table
union all
select subid,monthno,month,packagesnumber-1,paidamount
from expand_rows where packagesnumber >1
)
select expand_rows.*,(packagesnumber % numdays)+1 day, paidamount from expand_rows
join dayspermonth d on
d.month = expand_rows.month
order by subid, day
option (maxrecursion 0)
(packagesnumber % numdays)+1 является операцией по модулю, которая назначает элементы на день.
Обратите внимание, что я предварительно вычислил таблицу количества дней в каждом месяце для использования в запросе. Я также немного упростил задачу для ответа (добавил столбец с чистым месяцем, потому что не хотел возиться с копированием измерения даты).
Возможно, вам придется настроить запрос по модулю, если вам важно, где заканчиваются дополнительные элементы, когда они не делятся равномерно (например, если у вас есть 32 элемента в январе, у какого дня есть дополнительный элемент?). В этом примере второй день месяца имеет тенденцию получать наибольшее количество (из-за добавления 1 к учету того факта, что последний день месяца заканчивается 0). Если вы хотите, чтобы дополнительные дни сократились в начале месяца, вместо этого вы можете использовать выписку case, которая преобразует 0 в число дней в месяце.
Чтобы равномерно распределить 223 номера в дни января, мы делаем следующее: в январе 31 день. Остаток 223/3 - 6 223/31 - 7 (целочисленное деление).
Так вот 7 записей в день, плюс 1 дополнительная запись за 1-6 января.
Я использовал таблицу подсчета для создания дат и некоторых других, но распределение строк по дням может быть определено так:
with
tally as
(
select row_number() over (order by n)-1 n from
(values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) n(n)
cross join (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) m(m)
cross join (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) l(m)
cross join (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) k(m)
)
,t1 as
(
select
*
from
(values
(1 , 201701 , 223 , 'NO' , 'BCD' , 44803 , 2)
,(2 , 201701 , 61 , 'NO' , 'GHI' , 11934 , 2)
,(3 , 201701 , 929 , 'NO' , 'ABC' , 88714 , 2)
,(4 , 201701 , 470 , 'NO' , 'DEF' , 98404 , 2)
,(5 , 201702 , 223 , 'NO' , 'BCD' , 28225 , 2)
) t(subId , MonthNo , PackagesNumber , Country , EntryMethod , PaidAmount , Version)
)
,dates as
(
select dateadd(day,n,'20170101') as dt
,convert(varchar(10),dateadd(day,n,'20170101'),112)/100 mnthkey
,day(dateadd(day,-1,dateadd(month,1,cast(((convert(varchar(10),dateadd(day,n,'20170101'),112)/100)*100 +1) as varchar(10))))) DaysInMonth
from
tally
)
select
subId
,MonthNo
,dt
,PackagesNumber
,case when day(dt)<=PackagesNumber%DaysInMonth then 1 else 0 end remainder
,PackagesNumber/DaysInMonth evenlyspread
,Country
,EntryMethod
,PaidAmount
,Version
from t1 a
inner join dates b
on a.MonthNo=b.mnthkey
Я присоединяюсь к месяцу с таблицей данных, и для каждого дня месяца я назначаю равномерно распределенные дни, 7 в нашем примере и для первых дней, 6 в нашем примере, я добавляю 1 как остаток
Теперь у нас есть информация из вашей базовой таблицы, умноженная на каждый день в соответствующие месяцы, теперь нам просто нужно сделать несколько строк в день, здесь мы снова используем таблицу подсчета:
with
tally as
(
select row_number() over (order by n)-1 n from
(values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) n(n)
cross join (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) m(m)
cross join (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) l(m)
cross join (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) k(m)
)
,t1 as
(
select
*
from
(values
(1 , 201701 , 223 , 'NO' , 'BCD' , 44803 , 2)
,(2 , 201701 , 61 , 'NO' , 'GHI' , 11934 , 2)
,(3 , 201701 , 929 , 'NO' , 'ABC' , 88714 , 2)
,(4 , 201701 , 470 , 'NO' , 'DEF' , 98404 , 2)
,(5 , 201702 , 223 , 'NO' , 'BCD' , 28225 , 2)
) t(subId , MonthNo , PackagesNumber , Country , EntryMethod , PaidAmount , Version)
)
,dates as
(
select dateadd(day,n,'20170101') as dt
,convert(varchar(10),dateadd(day,n,'20170101'),112)/100 mnthkey
,day(dateadd(day,-1,dateadd(month,1,cast(((convert(varchar(10),dateadd(day,n,'20170101'),112)/100)*100 +1) as varchar(10))))) DaysInMonth
from
tally
)
,forshow as
(
select
subId
,MonthNo
,dt
,PackagesNumber
,case when day(dt)<=PackagesNumber%DaysInMonth then 1 else 0 end remainder
,PackagesNumber/DaysInMonth evenlyspread
,Country
,EntryMethod
,(PaidAmount+0.0000)/(PackagesNumber*1.0000) PaidAmount
,Version
,PaidAmount TotalPaidAmount
from t1 a
inner join dates b
on a.MonthNo=b.mnthkey
)
select
subId
,dt [Date]
,Country
,1 Packages
,EntryMethod
,PaidAmount
,Version
-- the following rows are just for control
,remainder+evenlyspread toalday
,count(*) over (partition by subId,MonthNo,dt) calctotalday
,PackagesNumber
,count(*) over (partition by subId) calcPackagesNumber
,sum(PaidAmount)over (partition by subId) calcPaidAmount
,TotalPaidAmount
from forshow
inner join tally on n<(remainder+evenlyspread )
order by subId,MonthNo,dt
Я присоединяюсь к числу дней (равномерно распределенных + остаток) и получаю один ряд PR-пакета.
Я добавил несколько проверочных столбцов, чтобы убедиться, что я получаю 8 строк за первые 6 дней и всего 223 строки для нашего примера.