按状态统计指定日期区间内每日图书数量的SQL查询需求

现有表结构及数据 #

现有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');

需求说明 #

需编写SQL查询2022年11月1日-11月9日区间内，每日按图书状态统计对应数量，状态规则如下：

• 图书被出租（Rented）后未归还时，每日持续处于Rented状态；
• 图书归还（Returned）后到再次出租前，每日计为Returned状态；
• 若图书在区间开始前无任何转移记录，默认处于Returned状态；若在区间内首次记录为Rented，则从出租日起进入Rented状态。

单本图书（BookID 1）的状态统计逻辑示例：
11月1日出租后，1-4日均为Rented；11月5日归还，当日为Returned；11月6日再次出租，6-8日均为Rented；11月9日归还，当日为Returned。

预期结果 #

+────────────+────────+────────+────────+────────+────────+────────+────────+────────+────────+
| 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      |
+────────────+────────+────────+────────+────────+────────+────────+────────+────────+────────+

SQL解决方案 #

以下是基于SQL Server的实现代码，通过生成日期序列、匹配图书每日状态，最后进行透视得到结果：

WITH DateRange AS (
    -- 生成指定区间内的所有日期
    SELECT CAST('2022-11-01' AS DATE) AS DateVal
    UNION ALL
    SELECT DATEADD(DAY, 1, DateVal)
    FROM DateRange
    WHERE DateVal < '2022-11-09'
),
BookDailyStatus AS (
    -- 匹配每本图书在每日的状态
    SELECT 
        b.BookID,
        dr.DateVal,
        -- 判断当日状态：取最新的转移记录，若为Rented且未归还则为Rented，否则Returned
        CASE 
            WHEN ISNULL(t_latest.BookStatus, 'Returned') = 'Rented' 
                 AND NOT EXISTS (
                     SELECT 1 FROM Transfer t_return
                     WHERE t_return.BookID = b.BookID
                       AND t_return.TransferDate > t_latest.TransferDate
                       AND t_return.TransferDate <= dr.DateVal
                       AND t_return.BookStatus = 'Returned'
                 ) THEN 'Rented'
            ELSE 'Returned'
        END AS DailyStatus
    FROM Books b
    CROSS JOIN DateRange dr
    OUTER APPLY (
        -- 获取当前日期及之前的最新转移记录
        SELECT TOP 1 BookStatus, TransferDate
        FROM Transfer t
        WHERE t.BookID = b.BookID
          AND t.TransferDate <= dr.DateVal
        ORDER BY TransferDate DESC
    ) t_latest
),
StatusCounts AS (
    -- 按状态和日期统计数量
    SELECT 
        DailyStatus AS Status,
        FORMAT(DateVal, 'dd.MM') AS DateStr,
        COUNT(BookID) AS Count
    FROM BookDailyStatus
    GROUP BY DailyStatus, FORMAT(DateVal, 'dd.MM')
)
-- 透视结果，将日期转为列
SELECT 
    Status,
    ISNULL([01.11], 0) AS [01.11],
    ISNULL([02.11], 0) AS [02.11],
    ISNULL([03.11], 0) AS [03.11],
    ISNULL([04.11], 0) AS [04.11],
    ISNULL([05.11], 0) AS [05.11],
    ISNULL([06.11], 0) AS [06.11],
    ISNULL([07.11], 0) AS [07.11],
    ISNULL([08.11], 0) AS [08.11],
    ISNULL([09.11], 0) AS [09.11]
FROM StatusCounts
PIVOT (
    SUM(Count)
    FOR DateStr IN ([01.11], [02.11], [03.11], [04.11], [05.11], [06.11], [07.11], [08.11], [09.11])
) AS PivotTable
ORDER BY CASE Status WHEN 'Rented' THEN 1 ELSE 2 END;

内容的提问来源于stack exchange，提问作者milo2011