mysql - 计算 Oracle 表中特定值之间模式的出现次数

Question

例如，借助下表，

Rn  ID Pattern
1   C20
2   C21
3   C25
4   C25
5   C25
6   C25
7   C25
8   C21
9   C23
10  C20
11  C21
12  C25
13  C25
14  C25
15  C22
16  C21
17  C20

我想计算每个 C20 之间的 C25 模式。

结果应该是这样的

-->C25 的计数 b/n C20 的第 1 次和第 2 次出现 = 5
请参阅下面的引用

Rn  ID Pattern
1   C20
2   C21
3   C25
4   C25
5   C25
6   C25
7   C25
8   C21
9   C23
10  C20

--> C25 的计数 b/n C20 的第 2 次和第 3 次出现 = 3

10  C20
11  C21
12  C25
13  C25
14  C25
15  C22
16  C21
17  C20

提前致谢。

Answer 1

使用带有 IGNORE NULLS 选项的 LAG 分析函数找到范围开始处的前一行，然后使用 COUNT具有范围窗口的分析函数以获取计数:

SQL Fiddle

Oracle 11g R2 架构设置:

CREATE TABLE table_name ( rn, id ) AS
  SELECT  1,  'C20' FROM DUAL UNION ALL
  SELECT  2,  'C21' FROM DUAL UNION ALL
  SELECT  3,  'C25' FROM DUAL UNION ALL
  SELECT  4,  'C25' FROM DUAL UNION ALL
  SELECT  5,  'C25' FROM DUAL UNION ALL
  SELECT  6,  'C25' FROM DUAL UNION ALL
  SELECT  7,  'C25' FROM DUAL UNION ALL
  SELECT  8,  'C21' FROM DUAL UNION ALL
  SELECT  9,  'C23' FROM DUAL UNION ALL
  SELECT 10,  'C20' FROM DUAL UNION ALL
  SELECT 11,  'C21' FROM DUAL UNION ALL
  SELECT 12,  'C25' FROM DUAL UNION ALL
  SELECT 13,  'C25' FROM DUAL UNION ALL
  SELECT 14,  'C25' FROM DUAL UNION ALL
  SELECT 15,  'C22' FROM DUAL UNION ALL
  SELECT 16,  'C21' FROM DUAL UNION ALL
  SELECT 17,  'C20' FROM DUAL;

查询 1:

SELECT *
FROM   (
  SELECT t.*,
         COUNT( CASE id WHEN 'C25' THEN 1 END )
           OVER ( ORDER BY rn
                  ROWS BETWEEN (rn - prev_rn) PRECEDING AND CURRENT ROW )
           AS num_c25
  FROM   (
    SELECT t.*,
           LAG( CASE id WHEN 'C20' THEN rn END, 1, 1 )
             IGNORE NULLS OVER ( ORDER BY rn ) AS prev_rn
    FROM   table_name t
  ) t
)
WHERE  id = 'C20'

Results :

| RN |  ID | PREV_RN | NUM_C25 |
|----|-----|---------|---------|
|  1 | C20 |       1 |       0 |
| 10 | C20 |       1 |       5 |
| 17 | C20 |      10 |       3 |

另一种方法是使用分层查询:

查询 2:

SELECT *
FROM   (
  SELECT t.*,
         COUNT( CASE id WHEN 'C25' THEN 1 END ) OVER ( ORDER BY rn ROWS BETWEEN LEVEL - 1 PRECEDING AND CURRENT ROW ) AS num_c25,
         CONNECT_BY_ROOT rn AS start_rn,
         CONNECT_BY_ISLEAF AS isleaf
  FROM   table_name t
  START WITH id = 'C20'
  CONNECT BY PRIOR rn + 1 = rn
  AND   id <> 'C20'
)
WHERE isleaf = 1

Results :

| RN |  ID | NUM_C25 | START_RN | ISLEAF |
|----|-----|---------|----------|--------|
|  9 | C23 |       5 |        1 |      1 |
| 16 | C21 |       3 |       10 |      1 |
| 17 | C20 |       0 |       17 |      1 |