在 teradata sql 中完成数据透视 [英] Accomplish pivot in teradata sql
问题描述
假设我有一个返回如下值的查询:
Say I have a query that returns values like this:
id type value
aaa 1a 10
aaa 1b 20
aaa 1c 7
bbb 2a 10
bbb 1a 5
有 > 5000 万行和 240 种可能的类型".我想制作一个数据透视图,其中每个 id
有一行,每个 type
都是它自己的列:
There are > 50 million rows and 240 possible "types". I want to make a pivot where there is one row for each id
and each type
is it's own column:
id 1a 1b 1c 2a
aaa 10 20 7
bbb 5 10
我可以在 SQL Server 中执行此操作,但我不知道如何在 Teradata 中执行此操作.列太多了,我无法做出 CASE 陈述.但是,每个不同的 type
都在一个表字段中,如果有帮助的话.
I can do this in SQL server but I don't know how to do it in Teradata. There are too many columns for me to make CASE statements. However, each distinct type
is in a table field, if that's any help.
推荐答案
Teradata SQL 中没有数据透视功能.这里回答了一个类似的问题 - teradata sql 将多次出现转换为附加列.
There is no pivot function in Teradata SQL. A similar question was answered here - teradata sql pivot multiple occurrences into additional columns.
为了最好地实现您想要的结果而不必手动写出 250 个案例,您应该在某种循环或集合中使用有序分析函数.尝试从 Teradata Developer Exchange 搜索loop"标签 - http://developer.teradata.com/tag/loop
To best achieve what you wanted without having to write out 250 cases manually, you should use ordered analytical functions in some kind of a loop or a set. Try searching "loop" tag from Teradata Developer Exchange - http://developer.teradata.com/tag/loop
这是我的做法:使用另一种编程语言 (Python) 重复文本/预制 SQL,并将它的两个变量从 1 更改为 250 次 250 次,然后生成完整的长 sql.只重复SELECT DISTINCT id
和最后FROM mytable
行之间的部分:
Here's how I would do it: Use another programming language (Python) to reiterate over a text/premade SQL and change it's only two variables 250 times, from 1 to 250, and generate the full long sql. Only reiterate the part between SELECT DISTINCT id
and last FROM mytable
row:
SELECT DISTINCT
id
-- reiteration starts here
,(SELECT SUM(value) -- assuming you have unique types for every id
FROM (SELECT DISTINCT
id
,value
,type
FROM mytable
QUALIFY (RANK() OVER(PARTITION BY type ORDER BY id ASC))=1 -- variable 1
)
) AS type_1 -- variable 2
-- reiteration ends here
FROM mytable
你可以使用这个python:
You can use this python:
for i in range(1,251):
print " \
,(SELECT SUM(value) -- assuming you have unique types for every id \
FROM (SELECT DISTINCT \
id \
,value \
,type \
FROM mytable \
QUALIFY (RANK() OVER(PARTITION BY type ORDER BY id ASC))=%d -- variable 1 \
) \
) AS type_%d -- variable 2 \
" % (i,i)
这篇关于在 teradata sql 中完成数据透视的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!