如何在 Teradata 14 中对子字符串进行分组? [英] How to group substrings in Teradata 14?

问题描述

我在Teradata 14中有下表，我不允许自己编写程序和函数，但我可以使用strtok、strtok_split_to_table 等

I have the following table in Teradata 14 , I am not allowed to write procedures and functions myself, but i can use strtok, strtok_split_to_table etc

id  property
1   1234X (Yel), 2225Y (Red), 1234X (Gre),
2
3   1222Y (Pin), 
4   1134E (Yel), 4565Y (Whi), 1134E (Red), 2222Y (Red), 

如何对上表进行分组，以便每个对象的所有属性都列在一个括号中

How can I group the above table so that each object would have all attributes listed in one brackets

id  property
1   1234X (Yel Gre), 2225Y (Red), 
2   
3   1222Y (Pin ),
4   1134E (Yel Red), 4565Y (Whi), 2222Y (Red), 

属性代码始终是 5 个字符的字符串，例如1222Y .颜色代码始终为 3 个字符，例如密码

The property code is always a 5 character string, e.g. 1222Y . The color code is always 3 character , e.g. Pin

我尝试使用 这个解决方案 但出现错误 A 列或字符表达式大于最大大小

I tried using this solution but got an error A column or character expression is larger than max size

另外我尝试了 strtok_split_to_table 并且能够创建一个修改过的表，但不知道如何继续

In addition I tried strtok_split_to_table and was able to create a modified table, but do not how to proceed from that

推荐答案

为什么要将非规范化数据存储在 RDBMS 中，然后对其进行处理以创建更糟糕的非规范化输出?

Why do you store denormalized data in a RDBMS and then process it to create even worse denormalized output?

根据您发布的链接修改我的解决方案以使用 STRTOK_SPLIT_TO_TABLE 而不是递归:

Modifying my solution from the link you posted to utilize STRTOK_SPLIT_TO_TABLE instead of recursion:

SELECT
   id,
   MAX(CASE WHEN newpos = 1 AND newgrp <> '(),' THEN newgrp ELSE '' END) ||
   MAX(CASE WHEN newpos = 2 THEN newgrp ELSE '' END) ||
   MAX(CASE WHEN newpos = 3 THEN newgrp ELSE '' END) ||
   MAX(CASE WHEN newpos = 4 THEN newgrp ELSE '' END) ||
   MAX(CASE WHEN newpos = 5 THEN newgrp ELSE '' END) ||
   MAX(CASE WHEN newpos = 6 THEN newgrp ELSE '' END)
   -- add as many CASEs as needed
FROM
 ( 
   SELECT 
     id, 
     ROW_NUMBER() 
     OVER (PARTITION BY id
           ORDER BY newgrp) AS newpos,
     TRIM(a || ' (' ||
     MAX(CASE WHEN tokennum = 1 THEN b || ' ' ELSE '' END) ||
     MAX(CASE WHEN tokennum = 2 THEN b || ' ' ELSE '' END) ||
     MAX(CASE WHEN tokennum = 3 THEN b || ' ' ELSE '' END) ||
     MAX(CASE WHEN tokennum = 4 THEN b || ' ' ELSE '' END) ||
     MAX(CASE WHEN tokennum = 5 THEN b || ' ' ELSE '' END) ||
     MAX(CASE WHEN tokennum = 6 THEN b || ' ' ELSE '' END)
     -- add as many CASEs as needd
     ) || '), ' AS newgrp
   FROM 
    (
      SELECT
        id, tokennum,
        TRIM(SUBSTRING(token FROM 1 FOR POSITION('(' IN TRIM(token)||'(') - 1)) AS a,
        TRIM(TRAILING ')' FROM SUBSTRING(token FROM POSITION('(' IN token) + 1)) AS b
      FROM
        TABLE( STRTOK_SPLIT_TO_TABLE(vt.id, vt.property, ',')
        RETURNS (id INT,
                 tokennum INT, 
                 token VARCHAR(30) CHARACTER SET UNICODE
                )
             ) AS dt
    ) AS dt
   GROUP BY id, a
 ) AS dt
GROUP BY id;

如果您可以访问 TDStats.udfconcat 函数，则可以进一步简化(但有一种方法可以控制属性的顺序:

If you got access to the TDStats.udfconcat function it can be further simplified (but there's way to control the order of properties:

SELECT id, 
   CASE
     WHEN TRIM(TDStats.udfconcat(' ' || a || ' ' || b)) || ',' <> '(),'
     THEN TRIM(TDStats.udfconcat(' ' || a || ' ' || b)) || ','
     ELSE ''
   END
FROM 
 (
   SELECT
     id,
     TRIM(SUBSTRING(token FROM 1 FOR POSITION('(' IN TRIM(token)||'(') - 1)) AS a,
     '('|| OTRANSLATE(TDStats.udfconcat(TRIM(TRAILING ')' FROM SUBSTRING(token FROM POSITION('(' IN token) + 1))), ',', ' ') || ')'AS b
   FROM
     TABLE( STRTOK_SPLIT_TO_TABLE(vt.id, vt.property, ',')
     RETURNS (id INT,
              tokennum INT, 
              token VARCHAR(30) CHARACTER SET UNICODE
             )
          ) AS dt
   GROUP BY id, a
 ) AS dt
GROUP BY id;

大部分工作都是在正确的位置摆弄空格和逗号以获得​​所需的输出.

Most of the work was fiddling with the spaces and commas in the right place to get the requested output.

我永远不会在 RDBMS 中存储这样的数据.

Still i would never store data as such in a RDBMS.

这篇关于如何在 Teradata 14 中对子字符串进行分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！