背景
我一直在研究一些需要多天輪班的報告視圖，并且應該根據資料進行一些計算，但我有點卡在這里。一個典型的班次要么是 3 個日歷日，通常是 1 個半天和兩個全天，要么是一個由 2 個半天（結束和開始）和 5 個全天組成的整周。
規格
對于全天和半天，我有以下規格。這些規則是基于法規的，不能更改。
- 2個半天！= 1個全天，2個半天更“有價值”
- 給定一個
started_atiso datetime 和end_atiso datetime - 我想得到兩個數字，full_days 和 half_days
- 半天是
- 從12.00 或之后開始的范圍開始的一天
- 在 19.00 之前結束的范圍結束時的一天
- 一整天是
- 范圍內的一天（傳統 24 小時）
- 從12.00 之前開始的范圍開始的一天
- 在 19.00 或之后結束的范圍結束時的一天
我在想每一個全天和半天一行，或者一個帶有 half_days 和 full_days 的聚合行作為兩個單獨的列在視圖中將它與我的其他視圖連接起來是理想的。
簡化模型
我簡化了資料模型以省略不必要的列。
create table if not exists [trip]
(
trip_id integer
constraint trip_pk
primary key,
started_at text default (datetime('now')),
end_at text default (datetime('now'))
);
我有點不知道應該如何設計這個查詢。一個簡單的時間增量不起作用。
SQLFiddle 示例資料和答案：http ://sqlfiddle.com/#!5/de7551/2
uj5u.com熱心網友回復：
您可以使用計算日跨度（班次跨度的天數）的 CTE 來解決此問題。由于半天總是 1、2 或 0（只發生在結束和開始），我們實際上不需要單獨考慮每一天。
您可以使用 julianday 將日期作為數字來獲取，但是 julian days 從中午開始，因此您需要減去 0.5 才能獲得“實際”日期以進行計算。如果結束時間晚于每一天的開始時間，則設定結束日期以避免過長的跨度，并將結果四舍五入以包括部分天作為跨度日。
此時我們可以通過檢查結束和開始來計算半天數。要獲得全天數，我們只需從結果中減去半天。
with trip_spans as (
select
ceil(julianday(end_at)-0.5 - floor(julianday(started_at)-0.5)) day_span
, t.*
, (
iif(time(started_at) > time('12:00'), 1, 0)
iif(time(end_at) <= time('19:00'), 1, 0)
) half_days
from trip t
)
select
trip_spans.*
, day_span-half_days full_days
from trip_spans
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/435942.html