SQL:列出/骨料中的所有项目一个相应的事务ID
问题描述:
我在Vertica的DB下表有:SQL:列出/骨料中的所有项目一个相应的事务ID
+-----+------+
| Tid | Item |
+-----+------+
| 1 | A |
| 1 | B |
| 1 | C |
| 2 | B |
| 2 | D |
+-----+------+
而且我想这个表:
+-----+-------+-------+-------+
| Tid | Item1 | Item2 | Item3 |
+-----+-------+-------+-------+
| 1 | A | B | C |
| 2 | B | D | |
+-----+-------+-------+-------+
记住我不知道transaction_id(Tid)可以拥有的最大物料编号,并且每个Tid的物料数量不是固定的。我尝试使用连接,但无法让它正常工作。谢谢您的帮助。
答
有一个在Vertica的PIVOT没有能力。作为查询的一部分,不能立即定义列。你必须指定。
也许还有其他选项,例如使用UDX将它们汇总在一起,例如您可以在Stack Overflow answer中找到的内容。但是这会把他们放到一个单一的领域。
唯一的另一种选择是使用Python之类的东西在客户端构建数据透视表。否则,您必须有一种方法来为您的查询生成列列表。
对于我的例子,我假设你正在处理一个独特的(Tid,Item)集合。您可能需要修改以满足您的需求。
首先,你需要确定的最大数量,如果你需要支持的项目:
with Tid_count as (
select Tid, count(*) cnt
from mytable
group by 1
)
select max(cnt)
from Tid_count;
而且我们说,你必须支持为4,那么您需要生成一个SQL以转动大多数项目:
with numbered_mytable as (
select Tid,
Item,
row_number() over (partition by Tid order by Item) rn
from mytable
)
select Tid,
MAX(decode(rn,1,Item)) Item1,
MAX(decode(rn,2,Item)) Item2,
MAX(decode(rn,3,Item)) Item3,
MAX(decode(rn,4,Item)) Item4
from numbered_mytable
group by 1
order by 1;
或者,如果你不想生成SQL,但知道你永远不会有比X项目的更多,你可以创建一个静态的形式进入到X.
答
你可以试试这个:
Create table #table(id int,Value varchar(1))
insert into #table
select 1,'A'
union
select 1,'B'
union
select 1,'C'
union
select 2,'B'
union
select 2,'D'
select id,[1] Item1,[2] Item2,[3] Item3 from
(
select id,Dense_rank()over(partition by id order by value)Rnak,Value from #table
)d
Pivot
(Min(value) for Rnak in ([1],[2],[3]))p
drop table #table
这实际上有用!非常感谢。快速跟进:你会如何做相反的方式?这里的动机是,能够删除一行并将表格转换回来。这是用于关联模式挖掘的,但是对于这种情况,您希望某个特定项目处于您的模式中。这样你可以删除所有不包含你的物品的交易。 – valenzio
如果我理解正确,那么对于每个项目,您都必须完成一个工会。例子:'选择Tid,Item1作为Item ... from union全选Tid,Item2 from ... union全选Tid,Item3 from ...'。 – woot