按多维数据集分组不显示单个值

zc0qhyus  于 2021-05-27  发布在  Hadoop
关注(0)|答案(0)|浏览(287)

我有个问题:

select O_CUSTKEY, L_SUPPKEY, sum(L_QUANTITY)
from orders as o, LINEITEM as l
WHERE o.O_ORDERKEY = l.L_ORDERKEY AND (O_CLERK = "Clerk#000000522" OR O_CLERK = "Clerk#000000154")
group by o.O_CUSTKEY, l.L_SUPPKEY with cube
order by O_CUSTKEY, L_SUPPKEY;

有4行与此查询匹配,我想打印ocustkey、lsuppkey和匹配的lquantity,但是当我运行此代码时,它会聚合lquantity并显示4次总和。
问题是:对于order clerks(o#clerk)clerk#000000522、clerk#000000154,查找每个客户(o#custkey)、每个供应商(lŧsuppkey)、每个客户和供应商(oŧcustkey、lŧsuppkey)的订购零件总数,以及订购零件的总数。
我得到的输出与要求不符。有人能建议怎样做才能获得所需的输出,如以下所示:

ocustkey1     lsuppkey1    qty
ocustkey1     lsuppkey2    qty
ocustkey1                  total
ocustkey2     lsuppkey3    qty
ocustkey2     lsuppkey4    qty
ocustkey2                  total
                           grandtotal

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题