江阴网站建设,江阴网站制作,江阴网站设计,江阴SEO优化,江阴小程序开发-江阴雨辰互联

  1. JustNews 首页
  2. questions

sql - Complex pattern counting that adds to the count on rows 1,2,3,4,5 but not on row 6,7 - Stack Overflow

admin••questions•阅读1

Here is the code I have done so far:WITH RECURSIVE numbers(pk) AS (SELECT 1UNION ALLSELECT pk + 1 FROM

Here is the code I have done so far:

WITH RECURSIVE numbers(pk) AS (
  SELECT 1
  UNION ALL
  SELECT pk + 1 FROM numbers WHERE pk < 27
)
SELECT pk, (row_number() over (order by pk asc) -1) %7 +1 as i,
    (case when pk%7 =5 then pk
     when pk%7 =6 then pk-1
    when pk%7 = 0 then pk-2 else pk end) as p 
 FROM numbers;

so I want to start counting on rows 1,2,3,4,5 and stop the count on row 6,7, (column i in the above code gives the row numbers)

Example: meaning row 8 will have count = 6 instead of 8, and so on but every 6,7th row will have the same value of the count as shown in the expected results.

Expected Results: P is the pattern I want to generate, i is the row numbers

pk i p
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 5
7 7 5
8 1 6
9 2 7
10 3 8
11 4 9
12 5 10
13 6 10
14 7 10
15 1 11
16 2 12
17 3 13
18 4 14
19 5 15
20 6 15
21 7 15
22 1 16
23 2 17
24 3 18
25 4 19
26 5 20
27 6 20

Here is the code I have done so far:

WITH RECURSIVE numbers(pk) AS (
  SELECT 1
  UNION ALL
  SELECT pk + 1 FROM numbers WHERE pk < 27
)
SELECT pk, (row_number() over (order by pk asc) -1) %7 +1 as i,
    (case when pk%7 =5 then pk
     when pk%7 =6 then pk-1
    when pk%7 = 0 then pk-2 else pk end) as p 
 FROM numbers;

so I want to start counting on rows 1,2,3,4,5 and stop the count on row 6,7, (column i in the above code gives the row numbers)

Example: meaning row 8 will have count = 6 instead of 8, and so on but every 6,7th row will have the same value of the count as shown in the expected results.

Expected Results: P is the pattern I want to generate, i is the row numbers

pk i p
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 5
7 7 5
8 1 6
9 2 7
10 3 8
11 4 9
12 5 10
13 6 10
14 7 10
15 1 11
16 2 12
17 3 13
18 4 14
19 5 15
20 6 15
21 7 15
22 1 16
23 2 17
24 3 18
25 4 19
26 5 20
27 6 20
  • sql
  • snowflake-cloud-data-platform
Share Improve this question edited Feb 5 at 22:13 Dale K 27.3k15 gold badges56 silver badges83 bronze badges asked Feb 5 at 22:05 StackOneStackOne 2863 silver badges19 bronze badges
Add a comment  | 

1 Answer 1

Reset to default 1

Based on comment that i and p should not be part of recursive CTE. First i is calculated based on PK and then P is calculated based on i, which only increments p when i not in 6,7.

WITH RECURSIVE seq AS (
    SELECT 1 AS pk
    UNION ALL
    SELECT pk + 1
    FROM seq
    WHERE pk < 27
),
i_calc AS (
    SELECT seq.pk,
           CASE 
               WHEN MOD(seq.pk - 1, 7) = 0 THEN 1 
               ELSE MOD(seq.pk - 1, 7) + 1 
           END AS i
    FROM seq
),
p_calc AS (
    SELECT i_calc.pk,
           i_calc.i,
           SUM(CASE WHEN i_calc.i IN ( 6, 7) THEN 0 ELSE 1 END) OVER (ORDER BY i_calc.pk) AS p
    FROM i_calc
)
SELECT pk, i, p
FROM p_calc;

Output

PK I P
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 5
7 7 5
8 1 6
9 2 7
10 3 8
11 4 9
12 5 10
13 6 10
14 7 10
15 1 11
16 2 12
17 3 13
18 4 14
19 5 15
20 6 15
21 7 15
22 1 16
23 2 17
24 3 18
25 4 19
26 5 20
27 6 20

发布者：admin，转转请注明出处：http://www.yc00.com/questions/1739437520a4127250.html

adminadmin
0

相关推荐

发表回复

评论列表（0条）

  • 暂无评论

联系我们

400-800-8888

在线咨询： QQ交谈

邮件：admin@example.com

工作时间：周一至周五，9:30-18:30，节假日休息

关注微信