SQL中行数字之间的依赖关系

nhaq1z21 于 2022-09-18 发布在 Java

关注(0)|答案(1)|浏览(146)

对于每个类别(列名)，排序的行之间存在依赖关系(按列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吗？

oracle

来源：https://stackoverflow.com/questions/73715192/dependency-between-row-figures-in-sql

1条答案

按热度按时间

lg40wkob1#

我们可以在这里使用LAG()解析函数：

WITH cte AS (
    SELECT t.*, LAG(End_am, 1, Start_am) OVER (PARTITION BY Name ORDER BY dt) AS End_am_lag
    FROM table_name t
)

SELECT Name, dt, Start_am, End_am
FROM cte
WHERE Start_am <> End_am_lag;

赞(0）回复(0）举报 2022-09-18

我来回答

SQL中行数字之间的依赖关系

1条答案

相关问题

热门标签

最新问答