Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use PARTITION BY grouping in the database, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

I defined three tables under my SCHEMA and filled in the data: customer table (plch_customer), product table (plch_product), and sales table (plch_sales).

CREATE TABLE plch_customer (

Cust_id INTEGER PRIMARY KEY

, cust_name VARCHAR2 (100)

)

/

BEGIN

INSERT INTO plch_customer VALUES (100,100, 'Customer A')

INSERT INTO plch_customer 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_product VALUES (10 minutes Mouse`)

INSERT INTO plch_product VALUES (20 minutes Keyboard`)

INSERT INTO plch_product VALUES (30 minutes Monitor')

COMMIT

END

/

CREATE TABLE plch_sales (

Cust_id INTEGER NOT NULL

, prod_id INTEGER NOT NULL

, quantity NUMBER NOT NULL

)

/

BEGIN

INSERT INTO plch_sales VALUES (100,10,500)

INSERT INTO plch_sales VALUES (100,10,800)

INSERT INTO plch_sales VALUES (100,20,600)

INSERT INTO plch_sales VALUES (200,10,400)

INSERT INTO plch_sales VALUES (200,20,300)

INSERT INTO plch_sales VALUES (200,20,700)

INSERT INTO plch_sales VALUES (300,10,100)

INSERT INTO plch_sales VALUES (300,10,200)

INSERT INTO plch_sales VALUES (300,10,900)

COMMIT

END

/

We want a list showing the total quantity of each product sold to each customer, with the following requirements:

A product appears on the list only if and only if it is sold to at least one customer.

For the products that appear in the list, the quantity sold to each customer in the plch_ customer table is displayed, or 0 if a customer has not purchased the product.

The output is as follows:

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

Which of the following statements correctly implements this requirement?

(A)

SELECT s.cust_id cust_id

S.prod_id prod_id

SUM (s.quantity) total

FROM plch_sales s

GROUP BY

S.cust_id

S.prod_id

UNION ALL

SELECT c.cust_id cust_id

P.prod_id prod_id

0 total

FROM plch_customer c

(SELECT DISTINCT s.prod_id

FROM plch_sales s) p

WHERE 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) total

FROM test.plch_sales s

PARTITION BY (s.prod_id)

RIGHT OUTER JOIN test.plch_customer c

ON (c.cust_id = s.cust_id)

GROUP BY

C.cust_id

S.prod_id

ORDER BY

C.cust_id

S.prod_id

/

(C)

SELECT c.cust_id

S.prod_id

NVL (SUM (s.quantity), 0) total

FROM plch_sales s

PARTITION BY (s.prod_id)

LEFT OUTER JOIN plch_customer c

ON (c.cust_id = s.cust_id)

GROUP BY

C.cust_id

S.prod_id

ORDER BY

C.cust_id

S.prod_id

/

(D)

SELECT c.cust_id

S.prod_id

NVL (SUM (s.quantity), 0) total

FROM plch_customer c

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_id

ORDER BY

C.cust_id

S.prod_id

/

(E)

SELECT c.cust_id

P.prod_id

NVL (SUM (s.quantity), 0) total

FROM

Plch_customer c

CROSS JOIN plch_product p

LEFT OUTER JOIN plch_sales s

ON (s.cust_id = c.cust_id

AND s.prod_id = p.prod_id

)

GROUP BY

C.cust_id

P.prod_id

ORDER BY

C.cust_id

P.prod_id

/

(F)

SELECT s.cust_id

P.prod_id

NVL (SUM (s.quantity), 0) total

FROM

Plch_product p

LEFT OUTER JOIN plch_sales s

ON (s.prod_id = p.prod_id)

GROUP BY

S.cust_id

P.prod_id

ORDER BY

S.cust_id

P.prod_id

/

(G)

SELECT c.cust_id

S.prod_id

NVL (SUM (s.quantity), 0) total

FROM

Plch_customer c

LEFT OUTER JOIN plch_sales s

ON (s.cust_id = c.cust_id)

GROUP BY

C.cust_id

S.prod_id

ORDER BY

C.cust_id

S.prod_id

/

(h)

SELECT c.cust_id

P.prod_id

NVL (SUM (s.quantity), 0) total

FROM

Plch_customer c

CROSS JOIN (SELECT DISTINCT prod_id

FROM plch_sales) p

LEFT OUTER JOIN plch_sales s

ON (s.cust_id = c.cust_id

AND s.prod_id = p.prod_id

)

GROUP BY

C.cust_id

P.prod_id

ORDER BY

C.cust_id

P.prod_id

/

The above is all the contents of the article "how to use PARTITION BY grouping in the database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.