snowflake-cloud-data-platform - SQL 中具有相似列名的行中的求和值

Question

我有一个如下所示的 SQL 表:

<表类="s-表"><头>身份证来源INPUT_AOUTPUT_AINPUT_BOUTPUT_B<日>... INPUT_ZOUTPUT_ZMOD_DATE<正文>(字符串)(数字)(数字)(数字)(数字)(数字)...(数字)(数字)(日期)ABC-12399912.111.940.741.0...1.10.12023-01-19DEF-45611111.112.941.741.0...1.22.12023-01-19

我想获得如下所示的查询结果:

<表类="s-表"><头>身份证INPUT_SUMOUTPUT_SUM<正文>ABC-12380.779.7DEF-45680.882.8

做这件事的漫长道路似乎是这样的:

SELECT ID,
       (INPUT_A + INPUT_B + ... + INPUT_Z) AS INPUT_SUM,
       (OUTPUT_A + OUTPUT_B + ... + OUTPUT_Z) AS OUTPUT_SUM
FROM DB_NAME.S_NAME.T_NAME

它给出了正确的答案，但在正确的位置包含所有正确的列名称时似乎容易出错。

要获取相关列名称的列表，我可以使用它:

SELECT COLUMN_NAME
  FROM DB_NAME.INFORMATION_SCHEMA.COLUMNS
 WHERE LEFT(COLUMN_NAME, 5) = 'INPUT' AND TABLE_NAME = 'T_NAME'

我不确定如何利用这些列表来改进原始查询。

Answer 1

正确的数据建模应该是解决它的首选方法。

幸运的是，SQL 的表现力足以用“动态”UNPIVOT 处理这种情况。请注意，性能可能比显式声明列列表 (INPUT_A + INPUT_B + ... + INPUT_Z) 更差。

示例数据:

CREATE OR REPLACE TABLE tab(ID TEXT, SOURCE TEXT,
                            INPUT_A   DECIMAL(10,2), OUTPUT_A  DECIMAL(10,2),
                            INPUT_B   DECIMAL(10,2), OUTPUT_B  DECIMAL(10,2),
                            INPUT_Z   DECIMAL(10,2), OUTPUT_Z  DECIMAL(10,2),
                            MOD_DATE  TEXT)
AS
      SELECT 'ABC-123', 999, 12.1, 11.9, 40.7, 41.0, 1.1, 0.1, '2023-01-19'
UNION SELECT 'DEF-456', 111, 11.1, 12.9, 41.7, 41.0, 1.2, 2.1, '2023-01-19';

查询:

SELECT sub.ID, sub.SOURCE,
   SUM(CASE WHEN s.KEY LIKE 'INPUT%'  THEN VALUE::DECIMAL(10,2) END) AS INPUT_SUM,
   SUM(CASE WHEN s.KEY LIKE 'OUTPUT%' THEN VALUE::DECIMAL(10,2) END) AS OUTPUT_SUM
FROM (SELECT *, OBJECT_CONSTRUCT_KEEP_NULL(*) AS json FROM tab) AS sub
,TABLE(FLATTEN(INPUT=> sub.json)) AS s
GROUP BY sub.ID, sub.SOURCE;

输出: