数据库中PARTITION BY分组怎么用

小编给大家分享一下数据库中PARTITION BY分组怎么用，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！我在自己的SCHEMA下定义了三个表并填入数据：客户表(plch_customer)，产品表(plch_product)，销售表(plch_sales)CREATE TABLE plch_customer (cust_id INTEGER PRIMARY KEY, cust_nameVARCHAR2(100))/BEGIN INSERT INTO plch_customer VALUES (100, ‘Customer A’); INSERT INTO plch_c免费主机域名ustomer免费主机域名 VALUES (200, ‘Customer B’); INSERT INTO plch_customer VALUES (300, ‘Customer C’); INSERT INTO plch_customer VALUES (400, ‘Customer D’); COMMIT;END;/CREATE TABLE plch_product (prod_id INTEGER PRIMARY KEY, prod_name VARCHAR2(100))/BEGIN INSERT INTO plch_productVALUES (10,’Mouse’); INSERT INTO plch_productVALUES (20,’Keyboard’); INSERT INTO plch_productVALUES (30,’Monitor’); COMMIT;END;/CREATE TABLE plch_sales (cust_id INTEGER NOT NULL, prod_id INTEGER NOT NULL, quantityNUMBERNOT NULL)/BEGIN INSERT INTO plch_salesVALUES (100, 10, 500); INSERT INTO plch_salesVALUES (100, 10, 800); INSERT INTO plch_salesVALUES (100, 20, 600); INSERT INTO plch_salesVALUES (200, 10, 400); INSERT INTO plch_salesVALUES (200, 20, 300); INSERT INTO plch_salesVALUES (200, 20, 700); INSERT INTO plch_salesVALUES (300, 10, 100); INSERT INTO plch_salesVALUES (300, 10, 200); INSERT INTO plch_salesVALUES (300, 10, 900); COMMIT;END;/我们想要一个清单，显示每种产品卖给每位客户的总数量，并有如下需求：一种产品当且仅当卖给至少一个客户时才出现在清单中。对于清单中出现的产品，售予plch_customer表中的每位客户的数量都要显示，如果某客户没有购买该产品则显示0。输出如下： CUST_ID PROD_ID TOTAL————- ————- ————- 100 10 1300 100 20 600 200 10 400 200 20 1000 300 10 1200 300 20 0 400 10 0 400 20 0下列的哪些语句正确实现了这个需求？(A)SELECT s.cust_idcust_id, s.prod_idprod_id, SUM(s.quantity)totalFROM plch_sales sGROUP BY s.cust_id, s.prod_idUNION ALLSELECT c.cust_idcust_id, p.prod_idprod_id, 0totalFROM plch_customerc, ( SELECT DISTINCT s.prod_id FROM plch_sales s )pWHERE NOT EXISTS ( SELECT ‘1’ FROM plch_sales s2 WHERE s2.cust_id = c.cust_id AND s2.prod_id = p.prod_id )ORDER BY cust_id, prod_id/(B)SELECT c.cust_id, s.prod_id, NVL(SUM(s.quantity),0) totalFROM test.plch_sales s PARTITION BY (s.prod_id) RIGHT OUTER JOIN test.plch_customercON ( c.cust_id = s.cust_id )GROUP BY c.cust_id, s.prod_idORDER BY c.cust_id, s.prod_id/(C)SELECT c.cust_id, s.prod_id, NVL(SUM(s.quantity),0) totalFROM plch_sales s PARTITION BY (s.prod_id) LEFT OUTER JOINplch_customercON ( c.cust_id = s.cust_id )GROUP BY c.cust_id, s.prod_idORDER BY c.cust_id, s.prod_id/(D)SELECT c.cust_id, s.prod_id, NVL(SUM(s.quantity),0) totalFROMplch_customerc LEFT OUTER JOIN plch_sales s PARTITION BY (s.prod_id)ON ( c.cust_id = s.cust_id )GROUP BY c.cust_id, s.prod_idORDER BY c.cust_id, s.prod_id/(E)SELECT c.cust_id, p.prod_id, NVL(SUM(s.quantity),0)totalFROM plch_customerc CROSS JOIN plch_productp LEFT OUTER JOIN plch_saless ON ( s.cust_id = c.cust_id AND s.prod_id = p.prod_id )GROUP BY c.cust_id, p.prod_idORDER BY c.cust_id, p.prod_id/(F)SELECT s.cust_id, p.prod_id, NVL(SUM(s.quantity),0)totalFROM plch_productp LEFT OUTER JOIN plch_saless ON ( s.prod_id = p.prod_id )GROUP BY s.cust_id, p.prod_idORDER BY s.cust_id, p.prod_id/(G)SELECT c.cust_id, s.prod_id, NVL(SUM(s.quantity),0)totalFROM plch_customerc LEFT OUTER JOIN plch_saless ON ( s.cust_id = c.cust_id )GROUP BY c.cust_id, s.prod_idORDER BY c.cust_id, s.prod_id/(H)SELECT c.cust_id, p.prod_id, NVL(SUM(s.quantity),0)totalFROM plch_customerc CROSS JOIN (SELECT DISTINCT prod_id FROM plch_sales)p LEFT OUTER JOIN plch_saless ON ( s.cust_id = c.cust_id AND s.prod_id = p.prod_id )GROUP BY c.cust_id, p.prod_idORDER BY c.cust_id, p.prod_id/以上是“数据库中PARTITION BY分组怎么用”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注云技术行业资讯频道！

相关推荐: 怎么部署Hbase单机与伪分布式

这篇文章主要介绍“怎么部署Hbase单机与伪分布式”，在日常操作中，相信很多人在怎么部署Hbase单机与伪分布式问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么部署Hbase单机与伪分布式”的疑惑有所帮助！接下来，请跟着小编一…