sql—如何使用snowflake javascript存储过程或函数迭代表中的所有列？

uz75evzq 于 2021-07-27 发布在 Java

关注(0)|答案(1)|浏览(407)

我有一个包含100多列的雪花表，我正在尝试获取每列中所有不同值的计数，并最终将每列的所有计数连接到一个表中。如果我只在一个专栏上做这件事，它会是这样的：

SELECT DISTINCT "AGE", count(*) AS "Frequency"
FROM 
    db.schema.tablename
WHERE 
    "SURVEYDATE" < "2019-07-29"
GROUP BY
    AGE;

我知道用python做这件事有点微不足道（也许我应该用pyspark来做，我愿意接受建议），但是为了使我的团队更易于使用，并且在3亿行上做得更快，我想使用snowflake javascript过程语言来做如下事情：

create or replace procedure column_counts(table)
returns array
language javascript
as
$$
var num_columns = //get number of columns
var columns = [list of columns]
var results_array = [];

for (i = 0; i < num_columns; i++) {
    var col_count = snowflake.createStatement( {sqlText: 'SELECT DISTINCT columns[i], count(*) AS "Frequency" FROM 
    db.schema.tablename WHERE "SURVEYDATE" < "2019-07-29" GROUP BY columns[i]' }).execute(); //This returns a table of all distinct values in that column and their counts
    results_array.push([columns[i], col_count]) //I then want an array like [column_name[0...i], distinct_value[0....n], frequency]
    return results_array;
$$
;
CALL column_counts();

我对在snowflake和snowflake中使用这个过程语言还是相当陌生的，所以对于每个月都会出现的新表，我绝对愿意接受关于如何做到最好以及以可重复的方式的建议。

sql JavaScript stored-procedures snowflake-cloud-data-platform

来源：https://stackoverflow.com/questions/63159196/how-to-iterate-over-all-columns-in-a-table-using-snowflake-javascript-stored-pro

1条答案

按热度按时间

3zwjbxry1#

这是可能的，没有任何形式的程序代码。例如，通过使用json：

WITH cte AS ( -- here goes the table/query/view
  SELECT TOP 100 OBJECT_CONSTRUCT(*) AS json_payload
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
)
SELECT f.KEY, 
      COUNT(DISTINCT f."VALUE") AS frequency, 
      LISTAGG(DISTINCT  f."VALUE" ,',') AS distinct_values  -- debug
FROM cte
, LATERAL FLATTEN (input => json_payload) f
-- WHERE f.KEY IN ('column_name1', 'column_name2', ...) -- only specific columns
GROUP BY f.KEY;

输出：

+-----------------+-----------+------------------------------------------------+
|       KEY       | FREQUENCY |                DISTINCT_VALUES                 |
+-----------------+-----------+------------------------------------------------+
| O_ORDERPRIORITY |         5 | 2-HIGH,1-URGENT,5-LOW,4-NOT SPECIFIED,3-MEDIUM |
| O_ORDERSTATUS   |         3 | P,O,F                                          |
| O_SHIPPRIORITY  |         1 | 0                                              |
| ...             |       ... | ....                                           |
+-----------------+-----------+------------------------------------------------+

工作原理：
使用为每行生成json OBJECT_CONSTRUCT(*) 将json展平为键/值
按键分组并应用特定聚合函数 COUNT/COUNT(DISTINCT )/LISTAGG/MIN/MAX/... 提供每列/值分布的版本：

WITH cte AS (
  SELECT TOP 100 OBJECT_CONSTRUCT(*) AS json_payload
  FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
)
SELECT f.KEY, f."VALUE", COUNT(*) AS frequency
FROM cte
, LATERAL FLATTEN (input => json_payload) f
-- WHERE f.KEY IN ('column_name1', 'column_name2', ...) -- only specific columns
GROUP BY f.KEY, f."VALUE"
ORDER BY f.KEY, f."VALUE";

赞(0）回复(0）举报 2021-07-27

我来回答

sql—如何使用snowflake javascript存储过程或函数迭代表中的所有列？

1条答案

相关问题

热门标签

最新问答