sqlite 如何按日期查找一行中连续出现的数据？

jhdbpxl9 于 2023-10-23 发布在 SQLite

关注(0)|答案(2)|浏览(116)

我试图解决的问题是，我需要知道在给定日期（当前运行）开始的连续运行中，已显示了多少次“租户ID + TenantId（1A）”。我只关心它是否大于1。
我的应用程序在工作日运行并插入行。它记录当前运行日期/时间，还添加了上次运行的日期和时间（显然是从不同的表中检索的）。

表格

CREATE TABLE EmployeeHistory
(
   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   CurrentRun TEXT,
   LastRun TEXT,
   EmployeeId TEXT,
   TenantId TEXT
);

示例数据

DELETE FROM EmployeeHistory;

INSERT INTO EmployeeHistory ('CurrentRun', 'LastRun', 'EmployeeId', 'TenantId')

VALUES

-- August 19th run
( '2023-08-19 00:00:00.000000', '2023-08-18 00:00:00.000000', '1', 'A' ), -- Consecutive! (Employee exists in August 18th run)
( '2023-08-19 00:00:00.000000', '2023-08-18 00:00:00.000000', '3', 'A' ), -- Should not be included because was not in August 18th run
( '2023-08-19 00:00:00.000000', '2023-08-18 00:00:00.000000', '1', 'B' ), -- Should not be included because was not in August 18th run
( '2023-08-19 00:00:00.000000', '2023-08-18 00:00:00.000000', '2', 'B' ), -- Consecutive! (Employee exists in August 18th run)
( '2023-08-19 00:00:00.000000', '2023-08-18 00:00:00.000000', '4', 'A' ), -- Consecutive! (Employee exists in all runs)
( '2023-08-19 00:00:00.000000', '2023-08-18 00:00:00.000000', '2', 'A' ), -- Consecutive! (Employee exists in August 18th run)

-- August 18th run
( '2023-08-18 00:00:00.000000', '2023-08-17 00:00:00.000000', '1', 'A' ), -- Consecutive (Employee exists in August 19th run)!
( '2023-08-18 00:00:00.000000', '2023-08-17 00:00:00.000000', '2', 'A' ), -- Consecutive (Employee exists in August 19th run)!
( '2023-08-18 00:00:00.000000', '2023-08-17 00:00:00.000000', '2', 'B' ), -- Consecutive (Employee exists in August 19th run)!
( '2023-08-18 00:00:00.000000', '2023-08-17 00:00:00.000000', '4', 'A' ), -- Consecutive! (Employee exists in all runs)

-- August 17th run
( '2023-08-17 00:00:00.000000', '2023-08-17 00:00:00.000000', '3', 'A' ), -- Should not be included because was not in August 18th run
( '2023-08-17 00:00:00.000000', '2023-08-17 00:00:00.000000', '5', 'A' ), -- Should not be included because was not in August 18th run
( '2023-08-17 00:00:00.000000', '2023-08-17 00:00:00.000000', '6', 'A' ), -- Should not be included because was not in August 18th run
( '2023-08-18 00:00:00.000000', '2023-08-17 00:00:00.000000', '4', 'A' ); -- Consecutive! (Employee exists in all runs)

电流输出：

ID  CurrentRun                  LastRun                     EmployeeId  TenantId
1   2023-08-19 00:00:00.000000  2023-08-18 00:00:00.000000  1           A
2   2023-08-19 00:00:00.000000  2023-08-18 00:00:00.000000  3           A
3   2023-08-19 00:00:00.000000  2023-08-18 00:00:00.000000  1           B
4   2023-08-19 00:00:00.000000  2023-08-18 00:00:00.000000  2           B
5   2023-08-19 00:00:00.000000  2023-08-18 00:00:00.000000  4           A
6   2023-08-19 00:00:00.000000  2023-08-18 00:00:00.000000  2           A
7   2023-08-18 00:00:00.000000  2023-08-17 00:00:00.000000  1           A
8   2023-08-18 00:00:00.000000  2023-08-17 00:00:00.000000  2           A
9   2023-08-18 00:00:00.000000  2023-08-17 00:00:00.000000  2           B
10  2023-08-18 00:00:00.000000  2023-08-17 00:00:00.000000  4           A
11  2023-08-17 00:00:00.000000  2023-08-16 00:00:00.000000  3           A
12  2023-08-17 00:00:00.000000  2023-08-16 00:00:00.000000  5           A
13  2023-08-17 00:00:00.000000  2023-08-16 00:00:00.000000  6           A
14  2023-08-18 00:00:00.000000  2023-08-17 00:00:00.000000  4           A

我已经谷歌和阅读了一些关于铅和滞后，但我不知道如何找回我想要的数据。
在我看来，一个解决方案是添加一个列来跟踪连续发生的事件，并根据上一次运行简单地增加给定员工的总和。这是个坏方法吗

sqlite

来源：https://stackoverflow.com/questions/76935682/how-to-find-consecutive-occurrences-of-data-in-a-row-by-date

2条答案

按热度按时间

iswrvxsc1#

您可以简单地使用EXISTS子句;这将给予您仅在CurrentRun +/- 1day下出现相同的租户ID +TenantId的示例：

select *
from EmployeeHistory A
where exists (
           select 1
           from EmployeeHistory B
           where A.EmployeeId=B.EmployeeId
             and A.TenantId=B.TenantId
             and (date(A.CurrentRun) = date(B.CurrentRun, '+1 day')
                  or 
                  date(A.CurrentRun) = date(B.CurrentRun, '-1 day'))
             )

你为什么把所有的东西都放在“文本”栏里？您可能需要针对数据类型转换进行调整。
或者.如果你想在输出中保留所有行，但只是标记连续的行：

select A.*
  ,case when 
            date(CurrentRun, '-1 day')=date(LAG(CurrentRun) over (partition by EmployeeId, TenantId order by CurrentRun)) 
         or date(CurrentRun, '+1 day')=date(LEAD(CurrentRun) over (partition by EmployeeId, TenantId order by CurrentRun)) 
     then 1 
     else 0 
   end as Consecutive
from EmployeeHistory A

赞(0）回复(0）举报 2023-10-23

sc4hvdpw2#

CREATE TABLE EmployeeHistory
(
   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   CurrentRun TEXT,
   LastRun TEXT,
   EmployeeId TEXT,
   TenantId TEXT
);

`进入登录历史记录（'当前运行'，'最后运行'，'登录ID'，'租户ID'）VALUES（'2023 - 08 - 19 00：00：00.000000'，'2023 - 08 - 18 00：00：00.000000'，' 1'，'A'），（' 2023 - 08 - 19 00：00：00.000000 '，' 2023 - 08 - 18 00：00：00.000000'，'3'，' A'），（'2023 - 08 - 19 00：00：00.000000'，'2023 - 08 - 18 00：00：00.000000'，' 1'，'B'），（' 2023 - 08 - 19 00：00：00.000000 '，' 2023 - 08 - 18 00：00：00.000000'，' 2'，' B'），（' 2023 - 08 - 19 00：00：00.000000'，' 2023 - 08 - 18 00：00：00.000000'，' 4'，' A'），（' 2023 - 08 - 19 00：00：00.000000'，' 2023 - 08 - 18 00：00：00.000000'，' 2'，' A'），（' 2023 - 08 - 18 00：00：00.000000'，' 2023 - 08 - 17 00：00：00.000000'，' 1'，' A'），（' 2023 - 08 - 18 00：00：00.000000'，' 2023 - 08 - 17 00：00：00.000000'，' 2'，' A'），（' 2023 - 08 - 18 00：00：00.000000'，' 2023 - 08 - 17 00：00：00.000000'，' 2'，' B'），（' 2023 - 08 - 18 00：00：00.000000'，' 2023 - 08 - 17 00：00：00.000000'，' 4'，' A'），（' 2023 - 08 - 17 00：00：00.000000'，' 2023 - 08 - 16 00：00：00.000000'，' 3'，' A'），（' 2023 - 08 - 17 00：00：00.000000'，' 2023 - 08 - 16 00：00：00.000000'，' 5'，' A'），（' 2023 - 08 - 17 00：00：00.000000'，' 2023 - 08 - 16 00：00：00.000000'，' 6'，' A'），（' 2023 - 08 - 18 00：00：00.000000'，' 2023 - 08 - 17 00：00：00.000000'，' 4'，' A'）;

✓``` 
SELECT a.*
FROM EmployeeHistory a
JOIN EmployeeHistory b
  ON a.EmployeeId = b.EmployeeId
  AND a.TenantId = b.TenantId
  AND a.LastRun = b.CurrentRun
WHERE a.CurrentRun >= '2023-08-17 00:00:00.000000' -- Starting date for the runs
ORDER BY a.CurrentRun DESC;

| ID|当前运行|最后运行|EmployeeID| tenantId|
| - -|- -|- -|- -|- -|
| 1| 2023 - 08 - 19 00：00：00.000000| 2023 - 08 - 18 00：00：00.000000| 1|一|
| 4| 2023 - 08 - 19 00：00：00.000000| 2023 - 08 - 18 00：00：00.000000| 2| B|
| 5| 2023 - 08 - 19 00：00：00.000000| 2023 - 08 - 18 00：00：00.000000| 4|一|
| 5| 2023 - 08 - 19 00：00：00.000000| 2023 - 08 - 18 00：00：00.000000| 4|一|
| 6| 2023 - 08 - 19 00：00：00.000000| 2023 - 08 - 18 00：00：00.000000| 2|一|

WITH RankedRuns AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY EmployeeId, TenantId ORDER BY CurrentRun DESC) AS rn
  FROM EmployeeHistory
)
SELECT a.ID, a.CurrentRun, a.LastRun, a.EmployeeId, a.TenantId
FROM RankedRuns a
JOIN RankedRuns b
  ON a.EmployeeId = b.EmployeeId
  AND a.TenantId = b.TenantId
  AND a.LastRun = b.CurrentRun

| ID|当前运行|最后运行|EmployeeID| tenantId||：-|：-----------|：--------|：-----------|：---------||1| 2023 - 08 - 19 00：00：00.000000| 2023 - 08 - 18 00：00：00.000000| 1|一||6| 2023 - 08 - 19 00：00：00.000000| 2023 - 08 - 18 00：00：00.000000| 2|一||4| 2023 - 08 - 19 00：00：00.000000| 2023 - 08 - 18 00：00：00.000000| 2| B||5| 2023 - 08 - 19 00：00：00.000000| 2023 - 08 - 18 00：00：00.000000| 4|一||5| 2023 - 08 - 19 00：00：00.000000| 2023 - 08 - 18 00：00：00.000000| 4|一|fiddle

赞(0）回复(0）举报 2023-10-23

我来回答

sqlite 如何按日期查找一行中连续出现的数据？

表格

示例数据

2条答案

相关问题

热门标签

最新问答