Каков наиболее эффективный способ создания сводки количества заказов по часам, дням, месяцам в SQL Server 2005?

Дана таблица:

create table #orders (
    orderid int,
    orderdatetime datetime
)

Как лучше всего написать sql для вывода отчета, содержащего количество заказов за текущие и предыдущие 24 часа, общее количество заказов за текущий день и предыдущие 7 дней, общее количество заказов за текущую неделю и предыдущие 4 недели, а также общее количество заказов за месяц и предыдущие 6 месяцев?

Мне интересно, можно ли эффективно свернуть это в один sql с помощью аналитических функций, или если 4 оператора sql, генерирующие 4 группы данных, являются единственным (или лучшим) способом.

Кроме того, учитывая группировку по часам/дням/неделям, как это сделать на сервере sql? Дата и время кажутся занозой в заднице каждый раз, когда мне приходится делать с ними что-то подобное...

Идеи? Может быть, поместить в куб SSAS и сделать это оттуда?


sql sql-server report sql-server-2005
person TheSoftwareJedi    schedule 24.06.2009    source источник
comment
Выводить в виде одной строки или нескольких строк?   -  person Peter Radocchia    schedule 24.06.2009
comment
@Peter уже ответил на вопрос - это не совсем то, что мне нужно, но я могу получить от этого нужную мне группировку и фильтрацию.   -  person TheSoftwareJedi    schedule 24.06.2009

Ответы (5)


arrow_upward
6
arrow_downward 

SELECT  DATEPART(month, orderdatetime), DATEPART(week, orderdatetime), DATEPART(day, orderdatetime), COUNT(*)
FROM    #orders
GROUP BY
        DATEPART(month, orderdatetime), DATEPART(week, orderdatetime), DATEPART(day, orderdatetime) WITH ROLLUP

Это сгруппирует COUNT по дням, неделям и месяцам в одном запросе.

Недельные сводки будут иметь NULL в столбце DATEPART(day, orderdatetime), месячные сводки будут иметь NULL в столбцах DATEPART(day, orderdatetime) и DATEPART(week, orderdatetime).

Чтобы сделать это за каждый час, день, неделю или месяц из текущего без пробелов, используйте CTE:

WITH    q_hours AS
        (
        SELECT  0 AS col_hour
        UNION ALL
        SELECT  col_hour + 1
        FROM    q_hours
        WHERE   col_hour < 22
        ),
        q_days AS
        (
        SELECT  0 AS col_day
        UNION ALL
        SELECT  col_day + 1
        FROM    q_days
        WHERE   col_day < 31
        ),
        q_months AS
        (
        SELECT  0 AS col_month
        UNION ALL
        SELECT  col_month + 1
        FROM    q_months
        WHERE   col_month < 12
        )
SELECT  col_month, col_day, col_hour, COUNT(orderid)
FROM    q_hours
CROSS JOIN
        q_days
CROSS JOIN
        q_months
LEFT JOIN
        #orders
ON      DATEDIFF(month, orderdatetime, GETDATE()) = col_month
        AND DATEDIFF(day, orderdatetime, GETDATE()) % 31 = col_day
        AND DATEDIFF(hour, orderdatetime, GETDATE()) % 24 = col_hour
GROUP BY
        col_month, col_day, col_hour WITH ROLLUP
HAVING  (
        col_month = 0
        AND col_day = 0
        AND col_hour IS NOT NULL
        ) -- all hours within 24 hours from now
        OR
        (
        col_month = 0
        AND col_day <= 7
        AND col_hour IS NULL
        ) -- all days within 7 days from now
        OR
        (
        col_month <= 6
        AND col_day IS NULL
        AND col_hour IS NULL
        ) -- all months within 6 months from now
person Quassnoi    schedule 24.06.2009
comment
Есть ли простой способ сделать так, чтобы он всегда был каждый час, помимо создания таблицы часов дня и присоединения к ней? - person TheSoftwareJedi; 24.06.2009
comment
В вопросе указано, что для каждой части даты существуют разные диапазоны - этот запрос даст вам количество всех из них только для одного диапазона. - person Scott Ivey; 24.06.2009
comment
Идеальное редактирование. Это было именно то, над чем я работал. Было почти готово, когда вы опубликовали это. Быстрее в следующий раз, пожалуйста. РЖУ НЕ МОГУ ;) - person TheSoftwareJedi; 24.06.2009
comment
ваше предложение with завалено ошибками копирования/вставки - person TheSoftwareJedi; 24.06.2009
comment
На самом деле, все решение просто не работает, потому что count(*) считает пустые часы за 1 и вообще игнорирует любые данные о заказе. - person TheSoftwareJedi; 24.06.2009
comment
вы сами запутались. вы имеете в виду день как дни назад и день месяца. так же и со всеми остальными... - person TheSoftwareJedi; 24.06.2009
comment
Текущее реализованное решение использует ТОЛЬКО часы, дни и месяцы с настоящего момента. Я оставил CTE как есть, чтобы упростить изменение условий HAVING всякий раз, когда возникает необходимость. - person Quassnoi; 24.06.2009
comment
Это решение не работает. Он ничего не засчитает › 1 день, потому что в этом случае col_hour = 24. :( ТААААА близко.... грр... - person TheSoftwareJedi; 29.06.2009
comment
@TheSoftwareJedi: попробуйте сейчас. Было бы намного проще, если бы вы разместили несколько примеров данных, чтобы я мог проверить, прежде чем отвечать. - person Quassnoi; 29.06.2009

arrow_upward
0
arrow_downward

Вы можете запустить четыре выборки из «фиктивной таблицы» или «идентификационной» таблицы, состоящей из одной строки.

Вы могли бы:

SELECT
    (<query count of orders current/prev 24 hours>) as <name1>,
    (<total orders current + 7 days>) as <name2>,
    (<total orders current week + 4 weeks>) as <name3>,
    (<total orders month + 6 months>) as <name4>
FROM
<IDENTITY table>;
person J. Polfer    schedule 24.06.2009

arrow_upward
0
arrow_downward

Поскольку вам нужны разные временные рамки для каждого типа даты, использование одного запроса со сводкой, вероятно, не даст вам того, что вы хотите. Я бы подумал о том, чтобы просто объединить их всех вместе, что-то вроде этого...

SELECT  DatePartValue = DATEPART(HH, orderdatetime), 
        Type = 'Hourly',
        COUNT(*)
FROM    #orders
WHERE   orderdatetime > DATEADD(HH, -25, GETDATE())
GROUP BY DATEPART(HH, orderdatetime)
UNION 
SELECT  DATEPART(DD, orderdatetime), 
        Type = 'Daily',
        COUNT(*)
FROM    #orders
WHERE   orderdatetime > DATEADD(DD, -8, GETDATE())
GROUP BY DATEPART(DD, orderdatetime)
UNION 
SELECT  DATEPART(WEEK, orderdatetime), 
        Type = 'Weekly',
        COUNT(*)
FROM    #orders
WHERE   orderdatetime > DATEADD(WEEK, -5, GETDATE())
GROUP BY DATEPART(WEEK, orderdatetime)
ORDER BY Type, DatePartValue
UNION 
SELECT  DATEPART(MM, orderdatetime), 
        Type = 'Monthly',
        COUNT(*)
FROM    #orders
WHERE   orderdatetime > DATEADD(MM, -7, GETDATE())
GROUP BY DATEPART(MM, orderdatetime)
ORDER BY Type, DatePartValue
person Scott Ivey    schedule 24.06.2009

arrow_upward
0
arrow_downward

Для результатов в одной строке примерно так:

select
  orders_day   = sum(case when datediff(hour,orderdatetime,getdate())  < 24 then 1 else 0 end)
, orders_week  = sum(case when datediff(day,orderdatetime,getdate())   < 7  then 1 else 0 end)
, orders_month = sum(case when datediff(week,orderdatetime,getdate())  < 4  then 1 else 0 end)
, orders_half  = sum(case when datediff(month,orderdatetime,getdate()) < 6  then 1 else 0 end)
from #orders

Вы можете точно настроить критерии даты, чтобы получить соответствующее поведение.

Для нескольких строк возьмите приведенные выше результаты и транспонируйте их с помощью UNPIVOT или CASE.. CROSS JOIN.

person Peter Radocchia    schedule 24.06.2009

arrow_upward
0
arrow_downward

Я думаю, вы хотите группировать наборы. Я понимаю, что сервер sql поддерживает наборы группировок.

EDIT1: я читал, что sql server 2005 не поддерживает наборы группировок, но sql server 2008 поддерживает. Здесь интересно прочитать о предполагаемой, но не существующей разнице между mapreduce и rdbms, такой как Oracle и Sql Server. Пожалуйста, прочтите и комментарии!! http://www.data-miners.com/blog/2008/01/mapreduce-and-sql-aggregations.html

person Theo    schedule 24.06.2009