主页 / dba / 问题 / 337516
Accepted
lifeisajourney
Asked: 2024-03-06 23:37:51 +0800 CST

Sql 查询以获取特定时间范围内的活跃产品

  • 772

我有下表

Create table #products
(
productId int,
regionId int,
date datetime,
active bit
)

insert into #products values
(1,20,'10/10/2020',1),(1,20,'12/10/2022',0),(1,20,'5/8/2023',1),
(2,20,'11/11/2020',0),(2,20,'12/12/2022',1),(2,20,'5/8/2023',0),
(3,20,'10/10/2020',1),(3,20,'12/10/2022',0),(3,20,'5/8/2023',1)

我试图找出哪些产品在 2023 年 3 月 1 日至 2023 年 3 月 30 日期间处于活动状态。查看提供的数据，在此期间只有产品 ID 2 处于活动状态。如何编写 SQL 查询来准确检索此信息？我尝试了一些查询，但没有得到我期望的结果。在此先感谢您的时间！

sql-server

2 个回答

  1. Best Answer
    CREATE TABLE #products (
  productId INT,
  regionId INT,
  date DATETIME,
  active BIT
  );
INSERT INTO #products VALUES
(1,20,'10/10/2020',1),(1,20,'12/10/2022',0),(1,20,'5/8/2023',1),
(2,20,'11/11/2020',0),(2,20,'12/12/2022',1),(2,20,'5/8/2023',0),
(3,20,'10/10/2020',1),(3,20,'12/10/2022',0),(3,20,'5/8/2023',1),
(4,20,'01/01/2020',1),
(5,20,'01/01/2020',0);
SELECT *, LEAD(date) OVER (PARTITION BY productId ORDER BY date) nextdate
FROM #products;
    产品编号 区域ID 日期 积极的 下一个日期
    1 20 2020-10-10 00:00:00.000 真的 2022-12-10 00:00:00.000
    1 20 2022-12-10 00:00:00.000 错误的 2023-05-08 00:00:00.000
    1 20 2023-05-08 00:00:00.000 真的 无效的
    2 20 2020-11-11 00:00:00.000 错误的 2022-12-12 00:00:00.000
    2 20 2022-12-12 00:00:00.000 真的 2023-05-08 00:00:00.000
    2 20 2023-05-08 00:00:00.000 错误的 无效的
    3 20 2020-10-10 00:00:00.000 真的 2022-12-10 00:00:00.000
    3 20 2022-12-10 00:00:00.000 错误的 2023-05-08 00:00:00.000
    3 20 2023-05-08 00:00:00.000 真的 无效的
    4 20 2020-01-01 00:00:00.000 真的 无效的
    5 20 2020-01-01 00:00:00.000 错误的 无效的 
    DECLARE @from DATE = '03/01/2023';
DECLARE @till DATE = '03/31/2023';

WITH cte AS (
  SELECT *, COALESCE(LEAD(date) OVER (PARTITION BY productId ORDER BY date), 
                     DATEADD(dd, 1, @till)) nextdate
  FROM #products
  )
SELECT *
FROM cte
WHERE date <= @till
  AND @from <= nextdate
  AND active = 1;
    产品编号 区域ID 日期 积极的 下一个日期
    2 20 2022-12-12 00:00:00.000 真的 2023-05-08 00:00:00.000
    4 20 2020-01-01 00:00:00.000 真的 2023-04-01 00:00:00.000

    小提琴

    如果您想单独处理每个区域的数据，请相应地扩展 PARTITION BY 表达式。

    • 1

  2. 我调整了我的查询，并根据 Akina 提供的建议，这就是我所拥有的。如果您认为此查询产生正确的结果，请告诉我。

    Declare @from date = '03/01/2021'
Declare @to   date = '03/31/2021'

;WITH MaxActiveDates AS (
SELECT productId
      ,MAX(date) AS max_active_date
  FROM #products
 WHERE active = 1
   AND regionId = 20
   AND date < @to
 GROUP BY productId
),
InactiveNextDates AS (
SELECT p.productId
      ,MIN(p.date) AS next_inactive_date
  FROM #products       p
  JOIN MaxActiveDates  m 
    ON p.productId   = m.productId
 WHERE p.active      = 0
   AND p.regionId    = 20
   AND p.date        > m.max_active_date
   AND p.date       >= @from
 GROUP BY p.productId
)
SELECT p.*
  FROM #products p
  JOIN MaxActiveDates m 
    ON p.productId = m.productId
  JOIN InactiveNextDates i 
    ON p.productId = i.productId
   AND p.date = m.max_active_date
    • 0

相关问题