我有幾個跟蹤的標本，它們可以全天（及其期間）在不同區域之間移動。在下面的示例中，mov每當樣本或日期發生變化時，計算欄位必須重新開始計數。如果period變化但仍然相同specimen，day則= 先前。如果兩者和保持不變并且只有變化，則= 前一個 1。areamovmovspecimendayareamovmov
像這樣：
我的問題是：
- 如何復制
mov周期變化但保持不變specimen的先前值？dayarea specimen當兩者都day保持不變并且只有變化時如何增加值area？
我在 x86_64-pc-linux-gnu 上使用 PostgreSQL 12.11 (Ubuntu 12.11-0ubuntu0.20.04.1)。
這是我到目前為止所做的：
DROP TABLE IF EXISTS mytable;
CREATE TABLE mytable(
specimen INTEGER,
day INTEGER,
period INTEGER,
area INTEGER
);
INSERT INTO mytable (specimen,day,period,area)
VALUES
(1,1,1,1),
(1,1,2,1),
(1,1,3,2),
(1,1,4,2),
(1,1,5,3),
(1,1,6,2),
(1,2,1,2),
(1,2,2,1),
(1,2,3,2),
(1,2,4,3),
(1,2,5,2),
(1,2,6,1),
(2,1,1,3),
(2,1,2,3),
(2,1,3,1);
SELECT
*,
CASE
WHEN previous_specimen Is NULL THEN 1
WHEN specimen != previous_specimen THEN 1
WHEN specimen = previous_specimen AND day != previous_day THEN 1
-- WHEN specimen = previous_specimen AND day = previous_day AND area = previous_area THEN LAG(mov,1) OVER (ORDER BY specimen,day,period,area) -- **repeat previous value**
-- WHEN specimen = previous_specimen AND day = previous_day AND area != previous_area THEN LAG(mov,1) OVER (ORDER BY specimen,day,period,area) 1 -- **add 1 to previous value**
ELSE NULL
END AS mov
FROM (
SELECT
*,
LAG(specimen,1) OVER (ORDER BY specimen,day,period,area) previous_specimen,
LAG(day,1) OVER (ORDER BY specimen,day,period,area) previous_day,
LAG(area,1) OVER (ORDER BY specimen,day,period,area) previous_area
FROM mytable
) t1;
uj5u.com熱心網友回復：
這個更簡單的查詢應該這樣做：
SELECT specimen, day, period, area
, count(*) FILTER (WHERE step)
OVER (PARTITION BY specimen, day ORDER BY period) AS mov
FROM (
SELECT *
, lag(area) OVER (PARTITION BY specimen, day ORDER BY period) <> area AS step
FROM tbl
) sub
ORDER BY specimen, day; -- optional
Ifperiod是一個遞增的數字，對于 each 沒有間隙(specimen, day)，這是等價的：
SELECT t1.*
, count(*) FILTER (WHERE t1.area <> t2.area)
OVER (PARTITION BY t1.specimen, t1.day ORDER BY t1.period) AS mov
FROM tbl t1
LEFT JOIN tbl t2 ON t2.specimen = t1.specimen
AND t2.day = t1.day
AND t2.period = t1.period - 1
;
db<>在這里擺弄
不確定哪個更快。無論哪種方式，索引(specimen, day, period)都會有幫助（很多）。
看：
- 形成具有相同值的連續行組
- 選擇最長的連續序列
關于聚合FILTER子句：
- 使用其他（不同的）過濾器聚合列
應該是最快的：
- 對于絕對性能，SUM 更快還是 COUNT 更快？
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/491564.html
標籤：sql PostgreSQL 窗函数 差距和岛屿
下一篇：SQL：日期范圍來自多行