SQL, чтобы получить среднее значение за день из общего месяца

У меня есть таблица, в которой перечислены итоги месяца (цели)

person      total                 month       
----------- --------------------- ----------- 
1001        114.00                201005      
1001        120.00                201006      
1001        120.00                201007      
1001        120.00                201008      
.
1002        114.00                201005      
1002        222.00                201006      
1002        333.00                201007      
1002        111.00                201008      
.
.

но месяц - целое число (!)

У меня также есть другая таблица со списком рабочих дней (календарь)

tran_date               day_type
----------------------- ---------------------------------
1999-05-01 00:00:00.000 WEEKEND
1999-05-02 00:00:00.000 WEEKEND
1999-05-03 00:00:00.000 WORKING_DAY
1999-05-04 00:00:00.000 WORKING_DAY

1999-06-01 00:00:00.000 .....
.
.
.

Что я хочу сделать, так это получить список дат со средним значением для этого дня на основе количества дней в месяце, где day_type равен «WORKING_DAY» / всего за месяц.

поэтому, если бы у меня было, скажем, 20 рабочих дней в 201005 году, я бы получил в среднем 114/20 за каждый рабочий день, а в остальные дни было бы 0.

что-то вроде

person   tran_date               day_avg
-------  ----------------------- ---------------------------------
1001     2010-05-01 00:00:00.000 0
1001     2010-05-02 00:00:00.000 0
1001     2010-05-03 00:00:00.000 114/2 (as there are two working days)
1001     2010-05-04 00:00:00.000 114/2 (as there are two working days)
.
.
.

Это должно быть сделано как CTE, так как это ограничение целевой системы (я могу сделать только один оператор), я могу начать с (Даты до

WITH 
Dates AS
(
    SELECT CAST('19990501' as datetime) TRAN_DATE
    UNION ALL
    SELECT TRAN_DATE + 1
    FROM Dates
    WHERE TRAN_DATE + 1 <= CAST('20120430' as datetime)
),
Targets as
(
   select CAST(cast(month as nvarchar) + '01' as dateTime) mon_start, 
            DATEADD(MONTH, 1, CAST(cast(month as nvarchar) + '01' as dateTime)) mon_end, 
             total
   from targets
)
select ????

sql common-table-expression group-by
person Preet Sangha    schedule 01.12.2011    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Примерные данные (могут отличаться):

select * into #totals from (
select '1001' as person, 114.00  as total, 199905 as month union
select '1001', 120.00, 199906 union
select '1001', 120.00, 199907 union
select '1001', 120.00, 199908  

) t

select * into #calendar from (
select cast('19990501' as datetime) as tran_date, 'WEEKEND' as day_type union
select '19990502', 'WEEKEND' union
select '19990503', 'WORKING_DAY' union
select '19990504', 'WORKING_DAY' union
select '19990505', 'WORKING_DAY' union
select '19990601', 'WEEKEND' union
select '19990602', 'WORKING_DAY' union
select '19990603', 'WORKING_DAY' union
select '19990604', 'WORKING_DAY' union
select '19990605', 'WORKING_DAY' union
select '19990606', 'WORKING_DAY' union
select '19990701', 'WORKING_DAY' union
select '19990702', 'WEEKEND' union
select '19990703', 'WEEKEND' union
select '19990704', 'WORKING_DAY' union
select '19990801', 'WORKING_DAY' union
select '19990802', 'WORKING_DAY' union
select '19990803', 'WEEKEND' union
select '19990804', 'WEEKEND' union
select '19990805', 'WORKING_DAY' union
select '19990901', 'WORKING_DAY'
) t

Оператор Select возвращает 0, если день «выходной» или не существует в таблице calendar. Помните, что MAXRECURSION — это значение от 0 до 32 767.

;with dates as ( 
    select cast('19990501' as datetime) as tran_date 
    union all 
    select dateadd(dd, 1, tran_date) 
    from dates where dateadd(dd, 1, tran_date) <= cast('20010101' as datetime) 
) 
select t.person , d.tran_date, (case when wd.tran_date is not null then t.total / w_days else 0 end) as day_avg 
from dates d 
left join #totals t on  
    datepart(yy, d.tran_date) * 100 + datepart(mm, d.tran_date) = t.month 
left join ( 
        select datepart(yy, tran_date) * 100 + datepart(mm, tran_date) as month, count(*) as w_days 
        from #calendar 
        where day_type = 'WORKING_DAY' 
        group by datepart(yy, tran_date) * 100 + datepart(mm, tran_date) 
) c on t.month = c.month  
left join #calendar wd on d.tran_date = wd.tran_date and wd.day_type = 'WORKING_DAY' 
where t.person is not null
option(maxrecursion 20000)
person Michał Powaga    schedule 01.12.2011
comment
Я обновил ваш ответ, чтобы избавиться от нежелательных результатов. Спасибо за это - это прекрасно! - person Preet Sangha; 01.12.2011

arrow_upward
1
arrow_downward

Вы можете рассчитать количество рабочих дней в месяц в подзапросе. Только подзапрос должен будет использовать group by. Например:

select   t.person
,        wd.tran_date
,        t.total / m.WorkingDays as day_avg
from     @Targets t
join     @WorkingDays wd
on       t.month =  convert(varchar(6), wd.tran_date, 112) 
left join
        (
        select  convert(varchar(6), tran_date, 112) as Month
        ,       sum(case when day_type = 'WORKING_DAY' then 1 end) as WorkingDays
        from    @WorkingDays
        group by
                convert(varchar(6), tran_date, 112)
        ) as  m
on      m.Month = t.month

Рабочий пример в SE Data.
Для магического числа 112 в convert, см. страницу MSDN.

person Andomar    schedule 01.12.2011
comment
Хороший ответ - я должен выбрать другой, так как он пропускает дни, когда среднее значение не применяется (нерабочие дни). Спасибо, я узнал новые трюки из этого ответа. - person Preet Sangha; 01.12.2011

arrow_upward
1
arrow_downward

Если я правильно понял ваш вопрос, следующий запрос должен сделать это:

SELECT
    *,
    ISNULL(
        (
            SELECT total
            FROM targets
            WHERE
                MONTH(tran_date) = month - ROUND(month, -2)
                AND c1.day_type = 'WORKING_DAY'
        ) /
        (
            SELECT COUNT(*)
            FROM calendar c2
            WHERE
                MONTH(c1.tran_date) = MONTH(c2.tran_date)
                AND c2.day_type = 'WORKING_DAY'
        ),
        0
    ) day_avg
FROM
    calendar c1

На простом английском:

  • Для каждой строки в calendar,
  • получить итог соответствующего месяца, если эта строка является рабочим днем ​​(иначе получить NULL),
  • получить количество рабочих дней в том же месяце
  • и разделить их.
  • Наконец, преобразуйте NULL (нерабочих дней) в 0.
person Branko Dimitrijevic    schedule 01.12.2011
comment
Спасибо, но это не работает, когда у меня больше одного отдельного человека. Извините, моя ошибка в вопросе, я должен был прояснить это. Хорошая попытка, хотя. Я многому научился из объяснения. - person Preet Sangha; 01.12.2011