从确切的数值找过渡 SQL

huangapple
go评论94阅读模式
英文:

Find transitions from exact values SQL

问题

  1. | 行号   | 类别     | 前一行号   | 前一类别   |
  2. | ------ | ------- | ----------- | ---------- |
  3. | 6      | Math    | 7           | Other      |
  4. | 11     | Biology | 12          | Other      |
  5. | 15     | Biology | 16          | Other      |
  6. | 19     | Physics | 20          | Others     |
英文:

There is a dataset. I would like to take first transitions from class not in (Other, Others) to class in (Other, Others)

  1. | row_id | class   |
  2. | ------ | ------- |
  3. | 1      | Math    |
  4. | 2      | Math    |
  5. | 3      | Math    |
  6. | 4      | Math    |
  7. | 5      | Math    |
  8. | 6      | Math    |
  9. | 7      | Other   |
  10. | 8      | Other   |
  11. | 9      | Other   |
  12. | 10     | Biology |
  13. | 11     | Biology |
  14. | 12     | Other   |
  15. | 13     | Other   |
  16. | 14     | Biology |
  17. | 15     | Biology |
  18. | 16     | Others  |
  19. | 17     | Others  |
  20. | 18     | Others  |
  21. | 19     | Physics |
  22. | 20     | Others  |

So the result will be :

  1. | row_id | class   | prev_row_id | prev_class |
  2. | ------ | ------- | ----------- | ---------- |
  3. | 6      | Math    | 7           | Other      |
  4. | 11     | Biology | 12          | Other      |
  5. | 15     | Biology | 16          | Other      |
  6. | 19     | Physics | 20          | Others     |

I have found how to detect LAST transition but not in history. I use PTRESTO.

答案1

得分: 1

可以在这里使用LEAD()分析函数：

<!-- 语言: sql -->

  1. WITH cte AS (
  2.     SELECT *, LEAD(class) OVER (ORDER BY row_id) AS lead_class,
  3.               LEAD(row_id) OVER (ORDER BY row_id) AS lead_row_id
  4.     FROM yourTable
  5. )
  7. SELECT
  8.     row_id,
  9.     class,
  10.     lead_row_id AS next_row_id,
  11.     lead_class AS next_class
  12. FROM cte
  13. WHERE class NOT IN ('Other', 'Others') AND
  14.       lead_class IN ('Other', 'Others')
  15. ORDER BY row_id;
英文:

We can use the LEAD() analytic function here:

<!-- language: sql -->

  1. WITH cte AS (
  2.     SELECT *, LEAD(class) OVER (ORDER BY row_id) AS lead_class,
  3.               LEAD(row_id) OVER (ORDER BY row_id) AS lead_row_id
  4.     FROM yourTable
  5. )
  7. SELECT
  8.     row_id,
  9.     class,
  10.     lead_row_id AS next_row_id,
  11.     lead_class AS next_class
  12. FROM cte
  13. WHERE class NOT IN (&#39;Other&#39;, &#39;Others&#39;) AND
  14.       lead_class IN (&#39;Other&#39;, &#39;Others&#39;)
  15. ORDER BY row_id;

答案2

得分: 0

可以自连接表，限制初始行不与其他行连接，并连接到其他行，使它们连续：

  1. select t.row_id, t.class, th.row_id prev_row_id , th.class prev_class
  2.   from my_table t
  3.   join my_table th 
  4.     on (th.row_id = t.row_id + 1 and th.class in ('Other', 'Others'))
  5.  where t.class not in ('Other', 'Others')

注意： 仅当ID连续时才有效

英文:

You can self join table limiting initial row to not others, and joind to other and them to be sequential:

  1. select t.row_id, t.class, th.row_id prev_row_id , th.class prev_class
  2.   from my_table t
  3.   join my_table th 
  4.     on (th.row_id = t.row_id + 1 and th.class in (&#39;Other&#39;, &#39;Others&#39;))
  5.  where t.class not in (&#39;Other&#39;, &#39;Others&#39;)

*Caution: this will work only if id is continuous

答案3

得分: 0

你可以使用窗口函数。例如，lag 函数：

  1. -- 样本数据
  2. with dataset(row_id, class) as(
  3.     values (1, '数学'),
  4.         (2, '数学'),
  5.         (6, '数学'),
  6.         (7, '其他'),
  7.         (9, '其他'),
  8.         (10, '生物'),
  9.         (11, '生物'),
  10.         (12, '其他'),
  11.         (13, '其他'),
  12.         (14, '生物'),
  13.         (15, '生物'),
  14.         (16, '其他'),
  15.         (17, '其他'),
  16.         (18, '其他'),
  17.         (19, '物理'),
  18.         (20, '其他')
  19. ),
  21. -- 查询部分
  22. with_prev as(
  23.  SELECT *,
  24.     lag(row_id) OVER w AS prev_row_id,
  25.     lag(class) OVER w AS prev_class
  26.  FROM dataset
  27.  WINDOW w AS (ORDER BY row_id) -- Trino 允许共享窗口
  28. )
  30. select *
  31. from with_prev
  32. where prev_class not like '其他%' and class like '其他%'; -- 或者在 ('其他', '其他人') 中

输出：

row_id class prev_row_id prev_class
7 其他 6 数学
12 其他 11 生物
16 其他人 15 生物
20 其他人 19 物理
英文:

You can use window functions. For example lag:

  1. -- sample data
  2. with dataset(row_id, class) as(
  3.     values (1,  &#39;Math&#39;),
  4.         (2,  &#39;Math&#39;),
  5.         (6,  &#39;Math&#39;),
  6.         (7,  &#39;Other&#39;),
  7.         (9,  &#39;Other&#39;),
  8.         (10, &#39;Biology&#39;),
  9.         (11, &#39;Biology&#39;),
  10.         (12, &#39;Other&#39;),
  11.         (13, &#39;Other&#39;),
  12.         (14, &#39;Biology&#39;),
  13.         (15, &#39;Biology&#39;),
  14.         (16, &#39;Others&#39;),
  15.         (17, &#39;Others&#39;),
  16.         (18, &#39;Others&#39;),
  17.         (19, &#39;Physics&#39;),
  18.         (20, &#39;Others&#39;)
  19. ),
  21. -- query parts
  22. with_prev as(
  23.  SELECT *,
  24.     lag(row_id) OVER w AS prev_row_id,
  25.     lag(class) OVER w AS prev_class
  26.  FROM dataset
  27.  WINDOW w AS (ORDER BY row_id) -- Trino allows sharing window
  28. )
  30. select *
  31. from with_prev
  32. where prev_class not like &#39;Other%&#39; and class like &#39;Other%&#39;; -- or in (&#39;Other&#39;, &#39;Others&#39;)

Output:

row_id class prev_row_id prev_class
7 Other 6 Math
12 Other 11 Biology
16 Others 15 Biology
20 Others 19 Physics

huangapple
  • 本文由 发表于 2023年3月31日 20:42:23
  • 转载请务必保留本文链接：https://go.coder-hub.com/75898668.html
  • presto
  • sql
  • window-functions
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定