Ежедневное количество записей на основе распределения статуса

У меня есть таблица с именем Books и таблица с именем Transfer со следующей структурой:

CREATE TABLE Books
(
  BookID int,
  Title varchar(150),
  PurchaseDate date,
  Bookstore varchar(150),
  City varchar(150)
);

INSERT INTO Books VALUES (1, 'Cujo', '2022-02-01', 'CentralPark1', 'New York');
INSERT INTO Books VALUES (2, 'The Hotel New Hampshire', '2022-01-08', 'TheStrip1', 'Las Vegas');
INSERT INTO Books VALUES (3, 'Gorky Park', '2022-05-19', 'CentralPark2', 'New York');

CREATE TABLE Transfer
    (
        BookID int,
        BookStatus varchar(50),
        TransferDate date
    );

INSERT INTO Transfer VALUES (1, 'Rented', '2022-11-01');
INSERT INTO Transfer VALUES (1, 'Returned', '2022-11-05');
INSERT INTO Transfer VALUES (1, 'Rented', '2022-11-06');
INSERT INTO Transfer VALUES (1, 'Returned', '2022-11-09');
INSERT INTO Transfer VALUES (2, 'Rented', '2022-11-03');
INSERT INTO Transfer VALUES (2, 'Returned', '2022-11-09');
INSERT INTO Transfer VALUES (2, 'Rented', '2022-11-15');
INSERT INTO Transfer VALUES (2, 'Returned', '2022-11-23');
INSERT INTO Transfer VALUES (3, 'Rented', '2022-11-14');
INSERT INTO Transfer VALUES (3, 'Returned', '2022-11-21');
INSERT INTO Transfer VALUES (3, 'Rented', '2022-11-25');
INSERT INTO Transfer VALUES (3, 'Returned', '2022-11-29');

Смотрите рабочий пример.

Я хочу сделать запрос для интервала дат (в данном случае 01.11 - 09.11), который возвращает количество книг за каждый день на основе BookStatus из Transfer, например:

+────────────+────────+────────+────────+────────+────────+────────+────────+────────+────────+
| Status     | 01.11  | 02.11  | 03.11  | 04.11  | 05.11  | 06.11  | 07.11  | 08.11  | 09.11  |
+────────────+────────+────────+────────+────────+────────+────────+────────+────────+────────+
| Rented     | 2      | 1      | 2      | 2      | 0      | 2      | 3      | 3      | 1      |
+────────────+────────+────────+────────+────────+────────+────────+────────+────────+────────+
| Returned   | 1      | 2      | 1      | 1      | 3      | 1      | 0      | 0      | 2      |
+────────────+────────+────────+────────+────────+────────+────────+────────+────────+────────+

Книга остается взятой напрокат до тех пор, пока она не будет возвращена, и считается «Возвращенной» каждый день, пока ее снова не сдадут в аренду.

Вот как будет выглядеть результат запроса для одной книги (BookID 1):

Вы пробовали что-нибудь? Или вы просите нас сделать все это за вас?

Dale K 18.11.2022 08:50

@DaleK Я не знаю, с чего начать, поэтому и спрашиваю здесь.

milo2011 18.11.2022 08:58

T-SQL дает вам PIVOT , но есть одна загвоздка. Столбцы, которые будут расширены, должны быть известны заранее. Чтобы расшириться до произвольного количества столбцов, вы должны создать запрос и выполнить его с помощью EXEC или sp_executesql

Stavros Zotalis 18.11.2022 09:09

Также смотрите stackoverflow.com/questions/10404348/…

Stavros Zotalis 18.11.2022 09:13

Для изучения SQL требуется время, и вы должны начать с основ и работать. Есть много руководств.

Dale K 18.11.2022 09:54
Как настроить Tailwind CSS с React.js и Next.js?
Как настроить Tailwind CSS с React.js и Next.js?
Tailwind CSS - единственный фреймворк, который, как я убедился, масштабируется в больших командах. Он легко настраивается, адаптируется к любому...
LeetCode запись решения 2536. Увеличение подматриц на единицу
LeetCode запись решения 2536. Увеличение подматриц на единицу
Увеличение подматриц на единицу - LeetCode
Переключение светлых/темных тем
Переключение светлых/темных тем
В Microsoft Training - Guided Project - Build a simple website with web pages, CSS files and JavaScript files, мы объясняем, как CSS можно...
Отношения "многие ко многим" в Laravel с методами присоединения и отсоединения
Отношения "многие ко многим" в Laravel с методами присоединения и отсоединения
Отношения "многие ко многим" в Laravel могут быть немного сложными, но с помощью Eloquent ORM и его моделей мы можем сделать это с легкостью. В этой...
В PHP
В PHP
В большой кодовой базе с множеством различных компонентов классы, функции и константы могут иметь одинаковые имена. Это может привести к путанице и...
Карта дорог Беладжар PHP Laravel
Карта дорог Беладжар PHP Laravel
Laravel - это PHP-фреймворк, разработанный для облегчения разработки веб-приложений. Laravel предоставляет различные функции, упрощающие разработку...
0
5
55
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Я вижу два возможных решения.

Динамическое решение

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

Используйте два оператора cross apply, каждый из которых выполняет функцию агрегирования count(), чтобы подсчитать количество переводов книг.

-- generate date range
with Dates as
(
  select convert(date, '2022-11-01') as TransferDate
    union all
  select dateadd(day, 1, d.TransferDate)
  from Dates d
  where d.TransferDate < '2022-11-10'
)
select  d.TransferDate,
        c1.CountRented,
        c2.CountReturned
from Dates d
-- count all rented books up till today, that have not been returned before today
cross apply ( select count(1) as CountRented
              from Transfer t1
              where t1.BookStatus = 'Rented'
                and t1.TransferDate <= d.TransferDate
                and not exists ( select 'x'
                                 from Transfer t2
                                 where t2.BookId = t1.BookId
                                   and t2.BookStatus = 'Returned'
                                   and t2.TransferDate > t1.TransferDate
                                   and t2.TransferDate <= d.TransferDate ) ) c1
-- count all returned books for today
cross apply ( select count(1) as CountReturned
              from Transfer t1
              where t1.BookStatus = 'Returned'
                and t1.TransferDate = d.TransferDate ) c2;

Результат:

TransferDate  CountRented  CountReturned
------------  -----------  -------------
2022-11-01    1            0
2022-11-02    1            0
2022-11-03    2            0
2022-11-04    2            0
2022-11-05    1            1
2022-11-06    2            0
2022-11-07    2            0
2022-11-08    2            0
2022-11-09    0            2
2022-11-10    0            0

Этот результат не является сводным результатом, описанным в вопросе. Однако для поворота этого динамического решения требуется динамический SQL, что не так уж и просто!

Статическое решение

Это даст точный результат, как описано в вопросе (включая форматирование даты), но требует, чтобы диапазон дат был полностью введен один раз.

Основные строительные блоки аналогичны динамическому решению выше:

  • Рекурсивное общее табличное выражение для генерации диапазона дат.
  • Два cross apply для выполнения счетных вычислений, как раньше.

Есть также:

  • Дополнительный cross join для дублирования диапазона дат для каждого BookStatus (избегайте значений NULL в результате).

  • Некоторые функции replace(), str() и datepart() форматируют даты.

  • Выражение case для объединения двух счетчиков в один столбец.

Решение, вероятно, не самое производительное, но оно обеспечивает требуемый результат. Если вы хотите проверить BookID=1, просто раскомментируйте лишние пункты фильтра WHERE.

with Dates as
(
    select convert(date, '2022-11-01') as TransferDate
        union all
    select dateadd(day, 1, d.TransferDate)
    from Dates d
    where d.TransferDate < '2022-11-10'
),
PivotInput as
(
    select  replace(str(datepart(day, d.TransferDate), 2),  space(1), '0') + '.' + replace(str(datepart(month, d.TransferDate), 2),  space(1), '0') as TransferDate,
            s.BookStatus as [Status],
            case when s.BookStatus = 'Rented' then sc1.CountRented else sc2.CountReturned end as BookStatusCount
    from Dates d
    cross join (values('Rented'), ('Returned')) s(BookStatus)
    cross apply (   select count(1) as CountRented
                    from Transfer t1
                    where t1.BookStatus = s.BookStatus
                      and t1.TransferDate <= d.TransferDate
                      --and t1.BookID = 1
                      and not exists (  select 'x'
                                        from Transfer t2
                                        where t2.BookId = t1.BookId
                                          and t2.BookStatus = 'Returned'
                                          and t2.TransferDate > t1.TransferDate
                                          and t2.TransferDate <= d.TransferDate ) ) sc1
    cross apply (   select count(1) as CountReturned
                    from Transfer t3
                    where t3.TransferDate = d.TransferDate
                      --and t3.BookID = 1
                      and t3.BookStatus = 'Returned' ) sc2
)
select  piv.*
from PivotInput pivi
pivot (sum(pivi.BookStatusCount) for pivi.TransferDate in (
[01.11],
[02.11],
[03.11],
[04.11],
[05.11],
[06.11],
[07.11],
[08.11],
[09.11],
[10.11])) piv;

Результат:

Status    01.11  02.11  03.11  04.11  05.11  06.11  07.11  08.11  09.11  10.11
Rented    1      1      2      2      1      2      2      2      0      0
Returned  0      0      0      0      1      0      0      0      2      0

Fiddle, чтобы увидеть вещи в действии.

Извините, если я был недостаточно ясен. Я попытаюсь пояснить на примере: книга с BookID 1, получившая статус «Взято напрокат» 01.11.2022, оставалась в этом статусе до 05.11.2022, когда она изменила статус на «Возвращена» в который оставался до 06.11.2022, когда он изменил статус на «Аренда», и оставался в этом статусе до 09.11.2022, когда он изменил статус на «Возвращен». Таким образом, эта книга должна отображаться как «1» каждый день, когда она была в статусе «Взято напрокат», и «1», когда она была в статусе «Возвращена».

milo2011 21.11.2022 11:24

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

milo2011 21.11.2022 11:24

Я понимаю, что вы имеете в виду: книга остается взятой напрокат до тех пор, пока ее не вернут, а как быть со статусом «Возвращена»? Остается ли книга считаться «Возвращенной» каждый день, пока она снова не будет сдана в аренду? Пожалуйста отредактируйте вопрос так, чтобы ожидаемый результат соответствовал данным выборки (можно включить 10 ноября для большей ясности).

Sander 22.11.2022 12:31

Да, книга считается «Возвращенной» каждый день до тех пор, пока она снова не будет сдана в аренду.

milo2011 23.11.2022 08:25

Я отредактировал вопрос и добавил пример для одной книги

milo2011 23.11.2022 14:01

Обновлено решение и скрипт в соответствии с запрошенным результатом.

Sander 25.11.2022 13:53

Спасибо, @Сандер! Еще один вопрос. Что делать, если у меня есть другие статусы, кроме «Арендовано» и «Возвращено»? Другой пример состояния: Неактивно, Зарезервировано. Добавить ли его в перекрестное соединение?

milo2011 29.11.2022 09:12

Добавьте дополнительные значения в cross join и предоставьте дополнительный cross apply для выполнения агрегации счетчиков с любыми необходимыми ограничениями (если они есть). Пример скрипки

Sander 30.11.2022 10:53

Другие вопросы по теме