如何在SQL（Snowflake）中为每个分组添加递增计数器

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

How to put an incremental counter for each group in SQL (Snowflake)

问题

我有以下表格 - 基本上是一组起始日期和结束日期之间的产品及其价格。起始日期和结束日期按升序排列。

  1. product start       end         price
  2. A       08/04/2022  12/04/2022  29
  3. A       13/04/2022  12/05/2022  45      
  4. A       13/05/2022  25/12/2022  45
  5. A       26/12/2022  16/05/2023  29
  6. A       17/05/2023  23/05/2023  29
  7. A       24/05/2023  31/12/9999  49

我想要的是一个计数器，它基本上检查价格是否与前一行（对于特定产品）不同。如果是，将计数器增加1，否则将计数器值复制到上面。输出应该如下所示：

  1. product start       end         price  counter
  2. A       08/04/2022  12/04/2022  29     1
  3. A       13/04/2022  12/05/2022  45     2      
  4. A       13/05/2022  25/12/2022  45     2
  5. A       26/12/2022  16/05/2023  29     3
  6. A       17/05/2023  23/05/2023  29     3
  7. A       24/05/2023  31/12/9999  49     4

任何帮助将不胜感激。谢谢。

英文:

I have the following table - essentially a product and its price between a set of start and end dates. The start and end dates are sorted in ascending order.

  1. product start       end         price
  2. A       08/04/2022  12/04/2022  29
  3. A       13/04/2022  12/05/2022  45      
  4. A       13/05/2022  25/12/2022  45
  5. A       26/12/2022  16/05/2023  29
  6. A       17/05/2023  23/05/2023  29
  7. A       24/05/2023  31/12/9999  49

What I want is to have a counter that essentially checks if the price has changed from its previous row (for a particular product). If yes, increment the counter by 1, else copy the counter value from above.
Output should look like below:

  1. product start       end         price  counter
  2. A       08/04/2022  12/04/2022  29     1
  3. A       13/04/2022  12/05/2022  45     2      
  4. A       13/05/2022  25/12/2022  45     2
  5. A       26/12/2022  16/05/2023  29     3
  6. A       17/05/2023  23/05/2023  29     3
  7. A       24/05/2023  31/12/9999  49     4

Any help will be highly appreciated. Thanks.

答案1

得分: 3

Snowflake支持CONDITIONAL_CHANGE_EVENT窗口函数，专为此设计：

对于窗口分区内的每一行，当当前行中参数expr1的值与前一行中的expr1的值不同时，返回一个窗口事件编号。 窗口事件编号从0开始，每次增加1，以表示在该窗口内迄今为止的更改次数。

  1. SELECT *, CONDITIONAL_CHANGE_EVENT(price) 
  2.           OVER(PARTITION BY product ORDER BY start_) + 1 AS counter
  3. FROM tab
  4. ORDER BY product, start_;

对于输入表：

  1. ALTER SESSION SET DATE_INPUT_FORMAT = 'DD/MM/YYYY';
  3. CREATE OR REPLACE TABLE tab(product TEXT, start_ DATE, end_ DATE, price INT)
  4. AS
  5. SELECT 'A','08/04/2022','12/04/2022', 29 UNION ALL
  6. SELECT 'A','13/04/2022','12/05/2022', 45 UNION ALL      
  7. SELECT 'A','13/05/2022','25/12/2022', 45 UNION ALL
  8. SELECT 'A','26/12/2022','16/05/2023', 29 UNION ALL
  9. SELECT 'A','17/05/2023','23/05/2023', 29 UNION ALL
  10. SELECT 'A','24/05/2023','31/12/9999', 49;

输出：

如何在SQL（Snowflake）中为每个分组添加递增计数器

英文:

Snowflake supports CONDITIONAL_CHANGE_EVENT windowed function that is exactly designed for it:

> Returns a window event number for each row within a window partition when the value of the argument expr1 in the current row is different from the value of expr1 in the previous row. The window event number starts from 0 and is incremented by 1 to indicate the number of changes so far within that window.

  1. SELECT *, CONDITIONAL_CHANGE_EVENT(price) 
  2.           OVER(PARTITION BY product ORDER BY start_) + 1 AS counter
  3. FROM tab
  4. ORDER BY product, start_;

For input table:

  1. ALTER SESSION SET DATE_INPUT_FORMAT = 'DD/MM/YYYY';
  3. CREATE OR REPLACE TABLE tab(product TEXT, start_ DATE, end_ DATE, price INT)
  4. AS
  5. SELECT 'A','08/04/2022','12/04/2022', 29 UNION ALL
  6. SELECT 'A','13/04/2022','12/05/2022', 45 UNION ALL      
  7. SELECT 'A','13/05/2022','25/12/2022', 45 UNION ALL
  8. SELECT 'A','26/12/2022','16/05/2023', 29 UNION ALL
  9. SELECT 'A','17/05/2023','23/05/2023', 29 UNION ALL
  10. SELECT 'A','24/05/2023','31/12/9999', 49;

Output:

如何在SQL（Snowflake）中为每个分组添加递增计数器

答案2

得分: 2

以下是您要翻译的代码部分：

  1. 您可以使用一个运行总和来检查价格的前一个值是否不等于产品的当前价格值的标志：
  3.     with t as
  4.     (
  5.       select *,
  6.        case
  7.          when lag(price, 1, price) over (partition by product order by start_dt) <> price
  8.          then 1 else 0
  9.        end as flag
  10.      from tbl
  11.     )
  12.     
  13.     select product, start_dt, end_dt, price,
  14.     (sum(flag) over (partition by product order by start_dt) + 1) counter
  15.     from t

demo（在SQL Server上）

英文:

You could use a running sum on a flag that checks if the previous value of the price is not equal to the current price value of a product:

  1. with t as
  2. (
  3.   select *,
  4.    case
  5.      when lag(price, 1, price) over (partition by product order by start_dt) <> price
  6.      then 1 else 0
  7.    end as flag
  8.  from tbl
  9. )
  11. select product, start_dt, end_dt, price,
  12. (sum(flag) over (partition by product order by start_dt) + 1) counter
  13. from t

demo (on SQL server)

答案3

得分: 0

使用 rank() over。

  1. select *, rank() over(order by price) counter
  2. from mytable

rank() over - 这个窗口子句将从1开始，并且如果下一个值相同，那么它将继续而不将1添加到排名值。

因此，计数器的值将为1、2、2、3、4，因为第2和第3个值相同。

英文:

Use rank() over.

  1. select *, rank() over(order by price) counter
  2. from mytable

rank() over - this window clause will start from 1, and if the next value is same, then it will continue and not add 1 to the rank value.

So, counter values will be 1,2,2,3,4 because 2nd and 3rd values are same.

huangapple
  • 本文由 发表于 2023年6月5日 11:10:38
  • 转载请务必保留本文链接：https://go.coder-hub.com/76403294.html
  • snowflake-cloud-data-platform
  • sql
匿名

发表评论

匿名网友

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

确定