sql - SQLite查询以查找多行之间的日期时间差异
问题描述
这是我在 SQLite 中的两个表的结构
CREATE TABLE user
(
id integer PRIMARY KEY,
name TEXT
);
CREATE TABLE attendanceTable
(
id Integer,
mydate datetime,
startJob boolean
);
- 如果
startJob
为 1,则表示员工正在开始工作,如果 startJob 为 0,则表示员工正在停止工作。
attendanceTable
按mydate
列排序
我希望个人员工的工作时间输出。
查询的输入可以是两个不同的日期,例如2021-08-20
和2021-08-22
我想知道“每个人工作了多少?”
输出应该是:
[id, name, userWorkedTime]
[1, Alice, 09:00]
[2, Bob, 07:00]
12:00 至 16:00 + 22:00 至 03:00 = 9 小时
13:00 至 17:00 + 12:00 至 15:00 = 7 小时
查询的输入2021-08-20
和2021-08-21
- 输出应该是:
[id, name, userWorkedTime]
[1, Alice, 09:00]
[2, Bob, 04:00]
12:00 至 16:00 + 22:00 至 03:00 = 9 小时
13:00 至 17:00 = 4 小时
Alice 可能在晚上 11 点开始工作,并在第二天凌晨 3 点结束工作[所以工作时间是 4 小时]
解决方案
我相信以下将实现您想要的结果:-
WITH
/* The date selection parameters - change as necessary */
cte_selection(selection_start,selection_end) AS (SELECT '2020-08-20','2020-08-22'),
/* Extract data per shift - aka combine start and end
note that extract is 1 day befor and 1 day after actual selection criteria
as previous/subsequent days may be relevant
*/
cte_part1(userid,name,periodstart,periodend,duration) AS
(
SELECT
user.id,
name,
strftime('%s',mydate),
strftime('%s',
(
SELECT mydate
FROM attendancetable
WHERE id = at.id
AND NOT startjob
AND mydate > at.mydate
ORDER BY mydate ASC
LIMIT 1
)
) AS endjob,
(strftime('%s',
(
SELECT mydate
FROM attendancetable
WHERE id = at.id
AND NOT startjob
AND mydate > at.mydate
ORDER BY mydate ASC
LIMIT 1
)
) - strftime('%s',at.mydate)) AS duration
FROM attendancetable AS at
JOIN user ON at.id = user.id
WHERE startjob
AND mydate
BETWEEN date
(
(SELECT selection_start FROM cte_selection)
,'-1 day'
)
AND date
(
(SELECT selection_end FROM cte_selection)
,'+1 day'
)
),
/* split times if period crosses a day*/
cte_part2(userid,name,periodstart,startdate,periodend,enddate,duration,startday_duration,nextday_duration) AS
(
SELECT
userid,
name,
periodstart,
date(periodstart,'unixepoch') AS startdate,
periodend,
date(periodend,'unixepoch') AS enddate,
duration,
CASE
WHEN date(periodstart,'unixepoch') = date(periodend,'unixepoch') THEN duration
ELSE strftime('%s',date(periodstart,'unixepoch')||'24:00:00') - periodstart
END AS startday_duration,
CASE
WHEN date(periodstart,'unixepoch') = date(periodend,'unixepoch') THEN 0
ELSE periodend - strftime('%s',date(periodend,'unixepoch')||'00:00:00')
END AS nextday_duration
FROM cte_part1
),
/* generate new rows for following days */
cte_part3(userid,name,periodstart,startdate,periodend,enddate,duration,startday_duration,nextday_duration) AS
(
SELECT
userid,
name,
strftime('%s',date(periodend,'unixepoch')||'00:00:00'),
date(periodend,'unixepoch'),
periodend,
enddate,
nextday_duration,
nextday_duration,
0
FROM cte_part2
WHERE nextday_duration
),
/* combine both sets */
cte_part4 AS (SELECT * FROM cte_part2 UNION ALL SELECT * FROM cte_part3)
/* Group the final data */
SELECT *,time(sum(startday_duration),'unixepoch') AS time_worked
FROM cte_part4
WHERE startdate BETWEEN (SELECT selection_start FROM cte_selection) AND (SELECT selection_end FROM cte_selection) GROUP BY userid
;
例如:-
和 :-
注意除 time_worked 之外的所有结果都是来自基础数据的任意值。但是,用户 ID 和名称将是正确的,因为它们对于每个组都是相同的。其他值将是组中的值。
- 您可以轻松地将更改应用于最终查询以包含或排除列。
完整的测试 SQL 是:-
DROP TABLE IF EXISTS user;
CREATE TABLE IF NOT EXISTS user (id integer PRIMARY KEY,name TEXT);
DROP TABLE IF EXISTS attendanceTable ;
CREATE TABLE attendanceTable(id Integer,mydate datetime,startJob boolean);
INSERT INTO user VALUES (1,'Alice'),(2,'Bob');
INSERT INTO attendanceTable VALUES
(1,'2020-08-20 12:00:00',1),
(2,'2020-08-20 13:00:00',1),
(1,'2020-08-20 16:00:00',0),
(2,'2020-08-20 17:00:00',0),
(1,'2020-08-20 22:00:00',1),
(1,'2020-08-21 03:00:00',0),
(2,'2020-08-22 12:00:00',1),
(2,'2020-08-22 15:00:00',0)
;
WITH
/* The date selection parameters - change as necessary */
cte_selection(selection_start,selection_end) AS (SELECT '2020-08-20','2020-08-22'),
/* Extract data per shift - aka combine start and end
note that extract is 1 day befor and 1 day after actual selection criteria
as previous/subsequent days may be relevant
*/
cte_part1(userid,name,periodstart,periodend,duration) AS
(
SELECT
user.id,
name,
strftime('%s',mydate),
strftime('%s',
(
SELECT mydate
FROM attendancetable
WHERE id = at.id
AND NOT startjob
AND mydate > at.mydate
ORDER BY mydate ASC
LIMIT 1
)
) AS endjob,
(strftime('%s',
(
SELECT mydate
FROM attendancetable
WHERE id = at.id
AND NOT startjob
AND mydate > at.mydate
ORDER BY mydate ASC
LIMIT 1
)
) - strftime('%s',at.mydate)) AS duration
FROM attendancetable AS at
JOIN user ON at.id = user.id
WHERE startjob
AND mydate
BETWEEN date
(
(SELECT selection_start FROM cte_selection)
,'-1 day'
)
AND date
(
(SELECT selection_end FROM cte_selection)
,'+1 day'
)
),
/* split times if period crosses a day*/
cte_part2(userid,name,periodstart,startdate,periodend,enddate,duration,startday_duration,nextday_duration) AS
(
SELECT
userid,
name,
periodstart,
date(periodstart,'unixepoch') AS startdate,
periodend,
date(periodend,'unixepoch') AS enddate,
duration,
CASE
WHEN date(periodstart,'unixepoch') = date(periodend,'unixepoch') THEN duration
ELSE strftime('%s',date(periodstart,'unixepoch')||'24:00:00') - periodstart
END AS startday_duration,
CASE
WHEN date(periodstart,'unixepoch') = date(periodend,'unixepoch') THEN 0
ELSE periodend - strftime('%s',date(periodend,'unixepoch')||'00:00:00')
END AS nextday_duration
FROM cte_part1
),
/* generate new rows for following days */
cte_part3(userid,name,periodstart,startdate,periodend,enddate,duration,startday_duration,nextday_duration) AS
(
SELECT
userid,
name,
strftime('%s',date(periodend,'unixepoch')||'00:00:00'),
date(periodend,'unixepoch'),
periodend,
enddate,
nextday_duration,
nextday_duration,
0
FROM cte_part2
WHERE nextday_duration
),
/* combine both sets */
cte_part4 AS (SELECT * FROM cte_part2 UNION ALL SELECT * FROM cte_part3)
/* Group the final data */
SELECT *,time(sum(startday_duration),'unixepoch') AS time_worked
FROM cte_part4
WHERE startdate BETWEEN (SELECT selection_start FROM cte_selection) AND (SELECT selection_end FROM cte_selection) GROUP BY userid
;
DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS attendanceTable ;
推荐阅读
- axios - 如何解决axios.then回调上的“'response'已定义但从未使用”
- spring - 具有 Unit 返回类型的 Spring Post 方法返回 200 而不是 204
- r - 如何命名循环中的元素?
- javascript - 如何在 Node.JS 代码中的套接字事件之外的范围内使用 let 变量?
- java - Java:此方法必须返回 double 类型的结果类型
- docker - docker代理路径中的nginx到子域
- python - 使用 xpath 获取图像
- python - 我不确定如何将这段代码正确地[使用 PyInstaller] 执行到 .exe 文件中
- c++ - 重载运算符?
- python - Psycopg2 将数据保存到哪里?