如何在SQL/Snowflake中创建列行相同的交叉表/系数表？ [英] How to create a crosstab / coefficient table where columns and rows are the same in SQL / Snowflake?

问题描述

我有一个类似

的表格

      col1 | col2 | col3 | col4 | col5
 id1 |  1     0      0      1      0
 id2 |  1     1      0      0      0
 id3 |  0     1      0      1      0
 id4 |  0     0      1      0      1
 id5 |  1     0      1      0      0
 id6 |  0     0      0      1      0
  .
  .
  .
 idN

如何创建查询以获得类似

的表

      col1 | col2 | col3 | col4 | col5
col1 |  3     1      1      1      0
col2 |  1     2      0      1      0
col3 |  1     1      2      0      1
col4 |  1     1      1      2      0
col5 |  0     0      1      0      1

其中，结果中的每个条目都是一列中某个值为1的列与另一列中值为1的列一起出现的次数。

我可以通过执行以下操作获得对角线值：

SELECT 
sum(col1), sum(col2), sum(col3), sum(col4), sum(col5)
FROM (
SELECT 
col1, col2, col3, col4, col5, col1 + col2 + col3 + col4 + col5 ) AS total
FROM (
SELECT 
      ROW_NUMBER()OVER(PARTITION BY id ORDER BY date) row_num, *
FROM (
SELECT DISTINCT(id), date, col1, col2, col3, col4, col5
FROM db.schema.table)
)
WHERE row_num = 1 AND total <= 1
ORDER BY total DESC);

我假设我必须做某种轴心或各种联合操作，但我似乎想不出来。

推荐答案

因为您不知道IDEA IOS要取消透视的确切列数，所以可以操作它们，然后再将它们旋转回来。这应该是可行的：

-- identify table columns
with table_columns_list as (
select column_name, ordinal_position
from information_schema.columns
where table_schema like 'schema' and table_name like 'table' 
   ),
-- unpivot the table and add row id 
flat_table as (
select * from ( select * , row_number() as row_id from my_table)
unpivot(value for column_name in (select column_name from table_columns_list)
),
-- calculate all matrix values
full_flat_table as ( 
select a.row_id as row_id , a.column_name as a_column_name, b.column_name as 
b_column_name, min(a.value,b.value) as value
from flat_table as a inner join  flat_table as b on a.row_id=b.row_id
)

select * 
from full_flat_table
pivot(sum(value) for a_column_name in (select column_name from 
table_columns_list))
as p
order by b_column_name; 

这篇关于如何在SQL/Snowflake中创建列行相同的交叉表/系数表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！