对于每个类别(列名),排序的行之间存在依赖关系(按列DT),例如行i上的end_am=行i+1上的start_am。
CREATE TABLE table_name (Name,dt,Start_am,End_am) AS
SELECT 'A', DATE '2000-01-04', FLOAT 0, FLOAT 20 FROM DUAL UNION ALL
SELECT 'A', DATE '2000-01-05', FLOAT 20, FLOAT 0 FROM DUAL UNION ALL
SELECT 'A', DATE '2000-01-08', FLOAT 0, FLOAT 15 FROM DUAL UNION ALL
SELECT 'A', DATE '2000-01-10', FLOAT 15, FLOAT 25 FROM DUAL UNION ALL
SELECT 'A', DATE '2000-01-11', FLOAT 333, FLOAT 25 FROM DUAL UNION ALL
SELECT 'A', DATE '2000-01-12', FLOAT 25, FLOAT 25 FROM DUAL UNION ALL
SELECT 'B', DATE '2001-02-05', FLOAT 1, FLOAT 2 FROM DUAL UNION ALL
SELECT 'B', DATE '2001-02-09', FLOAT 2, FLOAT 2 FROM DUAL UNION ALL
SELECT 'B', DATE '2001-02-10', FLOAT 2, FLOAT 0 FROM DUAL UNION ALL
SELECT 'B', DATE '2001-02-11', FLOAT 0, FLOAT 0 FROM DUAL UNION ALL
SELECT 'B', DATE '2001-02-12', FLOAT 0, FLOAT -1 FROM DUAL UNION ALL
SELECT 'B', DATE '2001-02-13', FLOAT -1, FLOAT 0 FROM DUAL UNION ALL
SELECT 'B', DATE '2001-02-14', FLOAT 0, FLOAT 0 FROM DUAL;
在上面的示例中,名称B的数据是一致的,而名称A在2000-01-11不匹配(起始额333与前一天结束额25)。
可以签入SQL吗?
1条答案
按热度按时间lg40wkob1#
我们可以在这里使用
LAG()
解析函数: