Как добавить пропущенные даты при расчете количества в таблице

ProgramBox

Как добавить пропущенные даты при расчете количества в таблице

Post author:admin
Запись опубликована:19 августа, 2022
Post category:Вопросы по программированию

#sql #sql-server #datetime #count #date-arithmetic

#sql #sql-сервер #datetime #количество #дата-арифметика

Вопрос:

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

ID	ItemId	Время продажи
1	1234	2020-12-01 12:44:22
2	1234	2020-12-01 17:12:22
3	1234	2020-12-02 12:44:22
4	1234	2020-12-04 17:12:22

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

ID	ItemId	Дата	Количество продаж
1	1234	2020-12-01	2
2	1234	2020-12-02	1
3	1234	2020-12-04	1

как мне включить дни, когда не было совершено ни одной продажи с нулевым количеством, как показано ниже.

ID	ItemId	Дата	Количество продаж
1	1234	2020-12-01	2
2	1234	2020-12-02	1
3	1234	2020-12-03	0
4	1234	2020-12-04	1

1. Вы хотите запросить продажи за день для каждого элемента отдельно ? Сначала я рекомендую ìd вообще избегать столбца, поскольку технические ключи часто являются более запутанными, чем информационные в результате. Пусть item_id будет ваш первый столбец и сначала отсортируйте ваш запрос по нему, а затем по date . Будьте особенно осторожны в те дни, когда образцы определенного товара не были проданы, поскольку могут быть null значения, которые могут позволить вам пропустить некоторые строки вашего результата!

2. Возможно, a left join в сочетании с coalesce() -function может выполнить для вас простую работу, но в настоящее время я сам ищу это

3. Вам нужен один и тот же ассортимент для каждого товара? Фиксированный диапазон? Диапазон, основанный только на первой и последней дате для каждого элемента?

Ответ №1:

Один из вариантов использует рекурсивный запрос для генерации дат. затем вы можете cross join использовать список отдельных элементов, доступных в таблице, и привести таблицу с помощью a left join . Последний шаг — агрегирование:

 with cte as (
    select min(convert(date, saletime)) as dt, max(convert(date, saletime)) as max_dt from mytable
    union all
    select dateadd(day, 1, dt), max_dt from cte where dt < max_dt
)
select c.dt, i.itemid, count(t.id) as sale_count
from cte c
cross join (select distinct itemid from mytable) i
left join mytable t 
    on  t.itemid = i.itemid
    and t.date >= c.dt
    and t.date <  dateadd(day, 1, c.dt)
group by c.dt, i.itemid

В реальной ситуации у вас, вероятно, была бы отдельная ссылочная таблица для хранения элементов, которую вы использовали бы вместо select distinct подзапроса.

Ответ №2:

 DECLARE @start_date DATETIME = '2020-11-28 00:00:00.000';
DECLARE @end_date DATETIME = '2020-12-13 00:00:00.000';

;WITH AllDays AS (SELECT @start_date AS [DATE]
                   UNION ALL
                   SELECT DATEADD(DAY, 1, [Date])
                   FROM   AllDays
                   WHERE  [Date] < @end_date),
     Items AS (SELECT distinct itemid from Sales)
     SELECT ROW_NUMBER() OVER (ORDER BY i.itemid, a.[DATE]) AS [ID], i.itemid as [ITEMID], a.[DATE],  count(s.itemid) AS [SALE COUNT]
     FROM Items i
     CROSS JOIN AllDays a
     LEFT JOIN Sales s ON a.[DATE] = convert(date, s.salestime) and i.itemid = s.itemid
     GROUP BY i.itemid, a.[DATE]
     ORDER BY i.itemid, a.[DATE]
     OPTION (MAXRECURSION 0)

Результаты (для двух идентификаторов элементов и 16 дней):

  ---- -------- ------------------------- ------------ 
| ID | ITEMID | DATE                    | SALE COUNT |
 ---- -------- ------------------------- ------------ 
| 1  | 1234   | 2020-11-28 00:00:00.000 | 0          |
| 2  | 1234   | 2020-11-29 00:00:00.000 | 0          |
| 3  | 1234   | 2020-11-30 00:00:00.000 | 0          |
| 4  | 1234   | 2020-12-01 00:00:00.000 | 2          |
| 5  | 1234   | 2020-12-02 00:00:00.000 | 1          |
| 6  | 1234   | 2020-12-03 00:00:00.000 | 0          |
| 7  | 1234   | 2020-12-04 00:00:00.000 | 1          |
| 8  | 1234   | 2020-12-05 00:00:00.000 | 0          |
| 9  | 1234   | 2020-12-06 00:00:00.000 | 0          |
| 10 | 1234   | 2020-12-07 00:00:00.000 | 0          |
| 11 | 1234   | 2020-12-08 00:00:00.000 | 0          |
| 12 | 1234   | 2020-12-09 00:00:00.000 | 0          |
| 13 | 1234   | 2020-12-10 00:00:00.000 | 0          |
| 14 | 1234   | 2020-12-11 00:00:00.000 | 0          |
| 15 | 1234   | 2020-12-12 00:00:00.000 | 0          |
| 16 | 1234   | 2020-12-13 00:00:00.000 | 0          |
| 17 | 1235   | 2020-11-28 00:00:00.000 | 0          |
| 18 | 1235   | 2020-11-29 00:00:00.000 | 0          |
| 19 | 1235   | 2020-11-30 00:00:00.000 | 0          |
| 20 | 1235   | 2020-12-01 00:00:00.000 | 0          |
| 21 | 1235   | 2020-12-02 00:00:00.000 | 0          |
| 22 | 1235   | 2020-12-03 00:00:00.000 | 0          |
| 23 | 1235   | 2020-12-04 00:00:00.000 | 1          |
| 24 | 1235   | 2020-12-05 00:00:00.000 | 0          |
| 25 | 1235   | 2020-12-06 00:00:00.000 | 0          |
| 26 | 1235   | 2020-12-07 00:00:00.000 | 0          |
| 27 | 1235   | 2020-12-08 00:00:00.000 | 0          |
| 28 | 1235   | 2020-12-09 00:00:00.000 | 0          |
| 29 | 1235   | 2020-12-10 00:00:00.000 | 0          |
| 30 | 1235   | 2020-12-11 00:00:00.000 | 0          |
| 31 | 1235   | 2020-12-12 00:00:00.000 | 0          |
| 32 | 1235   | 2020-12-13 00:00:00.000 | 0          |
 ---- -------- ------------------------- ------------

Ответ №3:

Один из подходов использует календарную таблицу, которая отслеживает все даты, которые вы хотите отобразить в своем отчете:

 WITH dates AS (
    SELECT CAST('20201201' AS date) AS dt
    UNION ALL
    SELECT DATEADD(dd, 1, dt)
    FROM dates
    WHERE DATEADD(dd, 1, dt) <= '20201231'
)

SELECT
    t.ITEMID,
    d.dt,
    COUNT(t.ID) AS [SALE COUNT]
FROM dates d
LEFT JOIN yourTable t
    ON CAST(t.SALETIME AS date) = d.dt
GROUP BY
    t.ITEMID,
    d.dt
ORDER BY
    d.dt;

1. Разве это не дало null бы ценности в дни без продаж?

2. Нет, этого не будет. COUNT ввод a NULL приводит к нулевому счету.

3. count(*) похоже, но count(field_name) тоже?

4. Пожалуйста, изучите документацию для COUNT функции в любой базе данных, которую вы используете.

5. Похоже, вы правы. Итак, я тоже кое-что узнал

Ответ №4:

Как уже отмечалось в других ответах, одним из решений является рекурсивный CTE. Вы специально хотите сделать это для одного itemid, поэтому я бы предложил:

 with dates as (
      select min(convert(date, saletime)) as dte, max(convert(date, saletime)) as max_dte
      from mytable
      union all
      select dateadd(day, 1, dt), max_dt
      from dates
      where dte < max_dte
    )
select c.dt, v.itemid, count(t.id) as sale_count
from dates d cross join
     (values (1234)) v(itemid) left join
     mytable t
     on t.itemid = v.itemid and
        t.date >= d.dte
        t.date <  dateadd(day, 1, c.dt)
group by d.dte, v.itemid;

Обратите внимание, что если у вас есть более 100 дней, то вам также необходимо добавить OPTION (MAXRECURSION 0) , чтобы избежать ошибки при генерации даты.

Если вам нужно решение для всех идентификаторов элементов, то ответ GMB будет лучшим ответом.

Метки: Как добавить пропущенные даты при расчете количества в таблице