這是我在 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 小時]
uj5u.com熱心網友回復：
我相信以下將實作您想要的結果：-
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 ;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/361356.html