在Oracle中将某列上方所有varchar值连接在一起。

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

Concatenate all varchar values of a column above a row in oracle

问题

I'll provide a translation of the content you provided:

我有一个使用情况，在其中有一列存储一些字符、flg列和一个category列。
根据flg，如果flg为1，则对于第i行，res1应该是第i行上方的行的字符连接（用''+''连接），以及第i行的Column1值。“上方的第i行”意味着SEQNCE<SEQNCE(i)。

如果flg为零，则res1=Column1。对行上方的连接仅限于Category相同的情况，如果Category发生变化（例如，Category=2 Column1='I'是一个新集合），则应从那一行开始连接的过程。

例如，下面是名为'dmy_tbl'的表格：

LISTAGG是一种用于按其他列分组连接字符串的选项，而LAG是用于获取行上方行的选项，我尝试了下面的查询，但没有得到预期的结果。而且我无法决定如何根据category分区。对于这个使用情况，欢迎任何建议。

  1. select column1,catg,flg,SEQNCE,
  2. case when ((flg=1 and res is null) or flg=0) then column1
  3. else column1||'+'||res end as result
  4. from
  5. (select column1,catg,flg,SEQNCE,
  6. case when flg=1 then lag(column1) 
  7. over(order by catg,SEQNCE asc) else column1 end as res 
  8. FROM dmy_tbl
  9. order by catg,SEQNCE asc);

内部查询中，res列仅获取上面的一行，不确定如何将LISTAGG嵌入到内部查询中以获取所有上面的行。

英文:

I have a use case wherein there is a column which stores some characters, flg column and a category column.
Based on flg, if flg is 1 then for ith row the res1 should be concatenation of characters (concatenated by ''+'') of rows above the ith row and the ith row Column1 value. "Above ith row" meaning SEQNCE<SEQNCE(i).

If flg is zero then res1=Column1. This concatenation of rows above a row is limited only until category is same, if Category changes(eg. Category=2 Column1='I' is a new set) it should start from that row the process of concatenation.

For eg. below is the table named ''dmy_tbl''

在Oracle中将某列上方所有varchar值连接在一起。

LISTAGG is one such option to concatenate strings group by other column & LAG is option for fetching rows above a row, I have tried below query but it does not yield expected result. Also I'm not able to make a decision on how to partition on category. Any suggestion for this use case is highly welcomed.

  1. select column1,catg,flg,SEQNCE,
  2. case when ((flg=1 and res is null) or flg=0) then column1
  3. else column1||&#39;+&#39;||res end as result
  4. from
  5. (select column1,catg,flg,SEQNCE,
  6. case when flg=1 then lag(column1) 
  7. over(order by catg,SEQNCE asc) else column1 end as res 
  8. FROM dmy_tbl
  9. order by catg,SEQNCE asc);

在Oracle中将某列上方所有varchar值连接在一起。

The inner query, res column is only fetching one row above, not sure on how to fit in LISTAGG in the inner query to fetch all above rows

答案1

得分: 1

你可以使用 MODEL 子句：

  1. 选择 列1,
  2.        指示器,
  3.        序列,
  4.        类别,
  5.        CASE 指示器
  6.        当 0
  7.        然后 列1
  8.        否则 结果
  9.        结束 AS 结果1
  10. 从   dmy_tbl
  11. 模型
  12.   按 (类别) 分区
  13.   按 (序列) 维度
  14.   度量 (
  15.     列1,
  16.     指示器,
  17.     CAST(NULL AS VARCHAR2(4000)) AS 结果
  18.   )  
  19.   规则 (
  20.     结果[1] = 列1[1],
  21.     结果[序列>1] = 结果[cv()-1] || '+' || 列1[cv()]
  22.   );

对于示例数据：

  1. 创建表 dmy_tbl (列1, 指示器, 序列, 类别) 作为
  2. 选择 'A', 1, 1, 1 FROM DUAL UNION ALL
  3. 选择 'B', 0, 2, 1 FROM DUAL UNION ALL
  4. 选择 'C', 0, 3, 1 FROM DUAL UNION ALL
  5. 选择 'D', 1, 4, 1 FROM DUAL UNION ALL
  6. 选择 'E', 1, 5, 1 FROM DUAL UNION ALL
  7. 选择 'F', 1, 6, 1 FROM DUAL UNION ALL
  8. 选择 'G', 0, 7, 1 FROM DUAL UNION ALL
  9. 选择 'H', 1, 8, 1 FROM DUAL UNION ALL
  10. 选择 'I', 1, 1, 2 FROM DUAL UNION ALL
  11. 选择 'J', 0, 2, 2 FROM DUAL UNION ALL
  12. 选择 'K', 1, 3, 2 FROM DUAL UNION ALL
  13. 选择 'L', 1, 4, 2 FROM DUAL;

输出：

列1 指示器 序列 类别 结果1
A 1 1 1 A
B 0 2 1 B
C 0 3 1 C
D 1 4 1 A+B+C+D
E 1 5 1 A+B+C+D+E
F 1 6 1 A+B+C+D+E+F
G 0 7 1 G
H 1 8 1 A+B+C+D+E+F+G+H
I 1 1 2 I
J 0 2 2 J
K 1 3 2 I+J+K
L 1 4 2 I+J+K+L
英文:

You can use a MODEL clause:

  1. SELECT column1,
  2.        indicator,
  3.        seqnce,
  4.        category,
  5.        CASE indicator
  6.        WHEN 0
  7.        THEN column1
  8.        ELSE res
  9.        END AS res1
  10. FROM   dmy_tbl
  11. MODEL
  12.   PARTITION BY (category)
  13.   DIMENSION BY (seqnce)
  14.   MEASURES (
  15.     column1,
  16.     indicator,
  17.     CAST(NULL AS VARCHAR2(4000)) AS res
  18.   )  
  19.   RULES (
  20.     res[1] = column1[1],
  21.     res[seqnce&gt;1] = res[cv()-1] || &#39;+&#39; || column1[cv()]
  22.   );

Which, for the sample data:

  1. CREATE TABLE dmy_tbl (column1, indicator, seqnce, category) AS
  2. SELECT &#39;A&#39;, 1, 1, 1 FROM DUAL UNION ALL
  3. SELECT &#39;B&#39;, 0, 2, 1 FROM DUAL UNION ALL
  4. SELECT &#39;C&#39;, 0, 3, 1 FROM DUAL UNION ALL
  5. SELECT &#39;D&#39;, 1, 4, 1 FROM DUAL UNION ALL
  6. SELECT &#39;E&#39;, 1, 5, 1 FROM DUAL UNION ALL
  7. SELECT &#39;F&#39;, 1, 6, 1 FROM DUAL UNION ALL
  8. SELECT &#39;G&#39;, 0, 7, 1 FROM DUAL UNION ALL
  9. SELECT &#39;H&#39;, 1, 8, 1 FROM DUAL UNION ALL
  10. SELECT &#39;I&#39;, 1, 1, 2 FROM DUAL UNION ALL
  11. SELECT &#39;J&#39;, 0, 2, 2 FROM DUAL UNION ALL
  12. SELECT &#39;K&#39;, 1, 3, 2 FROM DUAL UNION ALL
  13. SELECT &#39;L&#39;, 1, 4, 2 FROM DUAL;

Outputs:

COLUMN1 INDICATOR SEQNCE CATEGORY RES1
A 1 1 1 A
B 0 2 1 B
C 0 3 1 C
D 1 4 1 A+B+C+D
E 1 5 1 A+B+C+D+E
F 1 6 1 A+B+C+D+E+F
G 0 7 1 G
H 1 8 1 A+B+C+D+E+F+G+H
I 1 1 2 I
J 0 2 2 J
K 1 3 2 I+J+K
L 1 4 2 I+J+K+L

fiddle

答案2

得分: 0

从Oracle 12开始，您可以使用MATCH_RECOGNIZE和LISTAGG：

  1. SELECT MAX(CASE rn WHEN 1 THEN column1 END) AS column1,
  2.        MAX(CASE rn WHEN 1 THEN indicator END) AS indicator,
  3.        MAX(CASE rn WHEN 1 THEN seqnce END) AS seqnce,
  4.        category,
  5.        LISTAGG(column1, '&#39;+&#39;) WITHIN GROUP (ORDER BY column1) AS res1
  6. FROM   dmy_tbl
  7. MATCH_RECOGNIZE(
  8.   PARTITION BY category
  9.   ORDER BY seqnce DESC
  10.   MEASURES
  11.     COUNT(*) AS rn,
  12.     MATCH_NUMBER() AS mn
  13.   ALL ROWS PER MATCH
  14.   AFTER MATCH SKIP TO NEXT ROW
  15.   PATTERN ( zero | one any_row* )
  16.   DEFINE
  17.     zero AS indicator = 0,
  18.     one  AS indicator = 1
  19. )
  20. GROUP BY category, mn
  21. ORDER BY category, seqnce;

对于示例数据：

  1. CREATE TABLE dmy_tbl (column1, indicator, seqnce, category) AS
  2. SELECT '&#39;A&#39;', 1, 1, 1 FROM DUAL UNION ALL
  3. SELECT '&#39;B&#39;', 0, 2, 1 FROM DUAL UNION ALL
  4. SELECT '&#39;C&#39;', 0, 3, 1 FROM DUAL UNION ALL
  5. SELECT '&#39;D&#39;', 1, 4, 1 FROM DUAL UNION ALL
  6. SELECT '&#39;E&#39;', 1, 5, 1 FROM DUAL UNION ALL
  7. SELECT '&#39;F&#39;', 1, 6, 1 FROM DUAL UNION ALL
  8. SELECT '&#39;G&#39;', 0, 7, 1 FROM DUAL UNION ALL
  9. SELECT '&#39;H&#39;', 1, 8, 1 FROM DUAL UNION ALL
  10. SELECT '&#39;I&#39;', 1, 1, 2 FROM DUAL UNION ALL
  11. SELECT '&#39;J&#39;', 0, 2, 2 FROM DUAL UNION ALL
  12. SELECT '&#39;K&#39;', 1, 3, 2 FROM DUAL UNION ALL
  13. SELECT '&#39;L&#39;', 1, 4, 2 FROM DUAL;

输出：

COLUMN1 INDICATOR SEQNCE CATEGORY RES1
A 1 1 1 A
B 0 2 1 B
C 0 3 1 C
D 1 4 1 A+B+C+D
E 1 5 1 A+B+C+D+E
F 1 6 1 A+B+C+D+E+F
G 0 7 1 G
H 1 8 1 A+B+C+D+E+F+G+H
I 1 1 2 I
J 0 2 2 J
K 1 3 2 I+J+K
L 1 4 2 I+J+K+L

fiddle

英文:

From Oracle 12, you can use MATCH_RECOGNIZE and LISTAGG:

  1. SELECT MAX(CASE rn WHEN 1 THEN column1 END) AS column1,
  2.        MAX(CASE rn WHEN 1 THEN indicator END) AS indicator,
  3.        MAX(CASE rn WHEN 1 THEN seqnce END) AS seqnce,
  4.        category,
  5.        LISTAGG(column1, &#39;+&#39;) WITHIN GROUP (ORDER BY column1) AS res1
  6. FROM   dmy_tbl
  7. MATCH_RECOGNIZE(
  8.   PARTITION BY category
  9.   ORDER BY seqnce DESC
  10.   MEASURES
  11.     COUNT(*) AS rn,
  12.     MATCH_NUMBER() AS mn
  13.   ALL ROWS PER MATCH
  14.   AFTER MATCH SKIP TO NEXT ROW
  15.   PATTERN ( zero | one any_row* )
  16.   DEFINE
  17.     zero AS indicator = 0,
  18.     one  AS indicator = 1
  19. )
  20. GROUP BY category, mn
  21. ORDER BY category, seqnce;

Which, for the sample data:

  1. CREATE TABLE dmy_tbl (column1, indicator, seqnce, category) AS
  2. SELECT &#39;A&#39;, 1, 1, 1 FROM DUAL UNION ALL
  3. SELECT &#39;B&#39;, 0, 2, 1 FROM DUAL UNION ALL
  4. SELECT &#39;C&#39;, 0, 3, 1 FROM DUAL UNION ALL
  5. SELECT &#39;D&#39;, 1, 4, 1 FROM DUAL UNION ALL
  6. SELECT &#39;E&#39;, 1, 5, 1 FROM DUAL UNION ALL
  7. SELECT &#39;F&#39;, 1, 6, 1 FROM DUAL UNION ALL
  8. SELECT &#39;G&#39;, 0, 7, 1 FROM DUAL UNION ALL
  9. SELECT &#39;H&#39;, 1, 8, 1 FROM DUAL UNION ALL
  10. SELECT &#39;I&#39;, 1, 1, 2 FROM DUAL UNION ALL
  11. SELECT &#39;J&#39;, 0, 2, 2 FROM DUAL UNION ALL
  12. SELECT &#39;K&#39;, 1, 3, 2 FROM DUAL UNION ALL
  13. SELECT &#39;L&#39;, 1, 4, 2 FROM DUAL;

Outputs:

COLUMN1 INDICATOR SEQNCE CATEGORY RES1
A 1 1 1 A
B 0 2 1 B
C 0 3 1 C
D 1 4 1 A+B+C+D
E 1 5 1 A+B+C+D+E
F 1 6 1 A+B+C+D+E+F
G 0 7 1 G
H 1 8 1 A+B+C+D+E+F+G+H
I 1 1 2 I
J 0 2 2 J
K 1 3 2 I+J+K
L 1 4 2 I+J+K+L

fiddle

答案3

得分: 0

如果您不关心是否涉及PL/SQL，一个简单的函数可能会完成任务。

  1. SQL> CREATE OR REPLACE FUNCTION f_res (par_category IN NUMBER, par_seqnce NUMBER)
  2.   2     RETURN VARCHAR2
  3.   3  IS
  4.   4     l_row   test%ROWTYPE;
  5.   5     retval  VARCHAR2 (30);
  6.   6  BEGIN
  7.   7     SELECT *
  8.   8       INTO l_row
  9.   9       FROM test
  10.  10      WHERE     category = par_category
  11.  11            AND seqnce = par_seqnce;
  12.  12
  13.  13     IF l_row.flg = 0
  14.  14     THEN
  15.  15        retval := l_row.column1;
  16.  16     ELSE
  17.  17        SELECT LISTAGG (column1, '+') WITHIN GROUP (ORDER BY seqnce)
  18.  18          INTO retval
  19.  19          FROM test
  20.  20         WHERE     category = par_category
  21.  21               AND seqnce <= par_seqnce;
  22.  22     END IF;
  23.  23
  24.  24     RETURN retval;
  25.  25  END;
  26.  26  /
  28. 函数已创建。
  30. 测试：
  32. SQL>   SELECT t.*, f_res (t.category, t.seqnce) res1
  33.   2      FROM test t
  34.   3  ORDER BY t.category, t.seqnce;
  36. C     SEQNCE        FLG   CATEGORY RES1
  37. - ---------- ---------- ---------- ------------------------------
  38. A          1          1          1 A
  39. B          2          0          1 B
  40. C          3          0          1 C
  41. D          4          1          1 A+B+C+D
  42. E          5          1          1 A+B+C+D+E
  43. F          6          1          1 A+B+C+D+E+F
  44. G          7          0          1 G
  45. H          8          1          1 A+B+C+D+E+F+G+H
  46. I          1          1          2 I
  47. J          2          0          2 J
  48. K          3          1          2 I+J+K
  49. L          4          1          2 I+J+K+L
  51. 12 rows selected.
  53. SQL>
英文:

If you don't care whether PL/SQL is involved, a simple function might do the job.

  1. SQL&gt; CREATE OR REPLACE FUNCTION f_res (par_category IN NUMBER, par_seqnce NUMBER)
  2. 2     RETURN VARCHAR2
  3. 3  IS
  4. 4     l_row   test%ROWTYPE;
  5. 5     retval  VARCHAR2 (30);
  6. 6  BEGIN
  7. 7     SELECT *
  8. 8       INTO l_row
  9. 9       FROM test
  10. 10      WHERE     category = par_category
  11. 11            AND seqnce = par_seqnce;
  12. 12
  13. 13     IF l_row.flg = 0
  14. 14     THEN
  15. 15        retval := l_row.column1;
  16. 16     ELSE
  17. 17        SELECT LISTAGG (column1, &#39;+&#39;) WITHIN GROUP (ORDER BY seqnce)
  18. 18          INTO retval
  19. 19          FROM test
  20. 20         WHERE     category = par_category
  21. 21               AND seqnce &lt;= par_seqnce;
  22. 22     END IF;
  23. 23
  24. 24     RETURN retval;
  25. 25  END;
  26. 26  /
  27. Function created.

Testing:

  1. SQL&gt;   SELECT t.*, f_res (t.category, t.seqnce) res1
  2. 2      FROM test t
  3. 3  ORDER BY t.category, t.seqnce;
  4. C     SEQNCE        FLG   CATEGORY RES1
  5. - ---------- ---------- ---------- ------------------------------
  6. A          1          1          1 A
  7. B          2          0          1 B
  8. C          3          0          1 C
  9. D          4          1          1 A+B+C+D
  10. E          5          1          1 A+B+C+D+E
  11. F          6          1          1 A+B+C+D+E+F
  12. G          7          0          1 G
  13. H          8          1          1 A+B+C+D+E+F+G+H
  14. I          1          1          2 I
  15. J          2          0          2 J
  16. K          3          1          2 I+J+K
  17. L          4          1          2 I+J+K+L
  18. 12 rows selected.
  19. SQL&gt;

huangapple
  • 本文由 发表于 2023年6月27日 18:23:51
  • 转载请务必保留本文链接：https://go.coder-hub.com/76563909.html
  • oracle
  • sql
匿名

发表评论

匿名网友

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

确定