LoginSignup
0

More than 5 years have passed since last update.

sql oracle 「分母*分子」⇔「全体*部分」を表現する

Last updated at Posted at 2018-10-15

まえがき

付番のやり方をいろいろ調べていて、tipsとして簡単にupしておこうとおもいます。

参考文献

sql oracle recursive lesson@練習仕様

データ準備

init.sql

create table test_mole_deno as
WITH src_zone AS (
SELECT lpad(LEVEL,2,0) AS ZONE FROM dual CONNECT BY LEVEL <= 3
),src_area AS(
SELECT lpad(LEVEL,3,0) AS area FROM dual CONNECT BY LEVEL <= 3
),src_block AS(
SELECT lpad(LEVEL,4,0) AS BLOCK FROM dual CONNECT BY LEVEL <= 3
)
select * from src_zone,src_area,src_block
;

方法

対象行が属するグループのうち何番目に位置しているのかと捉えてみる。


SELECT
    s1.zone
    ,s1.area
    ,dense_rank() over (partition by zone order by area) as mole
    ,dense_rank() over (order by zone) as deno
FROM
    test_mole_deno s1
;

image.png


SELECT
    s1.zone
    ,s1.area
    ,s1.block
    ,dense_rank() over (partition by zone,area order by zone,area,block) as mole
    ,dense_rank() over (order by zone,area) as deno
FROM
    test_mole_deno s1
;

image.png

まとめ

型はこんな感じだとおもう。


SELECT
    ▼▼▼
    ,▲▲▲
    ,dense_rank() over (partition by ▼▼▼ order by ▼▼▼,▲▲▲) as mole
    ,dense_rank() over (order by ▼▼▼) as deno
FROM
    test_mole_deno s1
;

あとがき

ニュアンスは微妙に伝わりにくいかもしれないですが、
他にもこんな表現あります!みたいなコメントいただけたらうれしいです。

以上、ありがとうございました。

20181015追記

upした直後、具体例が良くないなーと思ったので、しっくりきたのを再upします。連投失礼します。

init.sql

DROP TABLE cumming;

CREATE TABLE cumming (
    rn                   NUMBER
    , cust_id              VARCHAR2(24 BYTE)
    , item                 VARCHAR2(24 BYTE)
    , price                NUMBER
    , purchase_date        VARCHAR2(8 BYTE)
    , qty                  NUMBER
    , seq                  NUMBER
    , sum_volume           NUMBER
    , sum_weight           NUMBER
    , upper_limit_volume   NUMBER
    , upper_limit_weight   NUMBER
)
;

SET DEFINE OFF;
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (1,'c_1QC9','p_pIJM',9891,'20181015',7,89101,245,455,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (2,'c_1QC9','p_p_J1',9741,'20181015',2,195401,178,168,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (3,'c_1QC9','p_LKLO',7552,'20181015',6,309001,384,276,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (4,'c_1QC9','p_Q73L',439,'20181015',4,317701,368,32,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (5,'c_1QC9','p_WBGQ',2805,'20181015',3,329301,285,81,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (6,'c_1QC9','p_4N7B',5431,'20181015',6,375101,234,222,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (7,'c_1QC9','p_p_JY',6736,'20181015',7,375701,455,518,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (8,'c_1QC9','p_p_EA',9074,'20181015',6,380001,426,354,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (9,'c_1QC9','p_p_I8',9381,'20181015',3,388501,21,126,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (10,'c_1QC9','p_pIC3',7256,'20181015',3,462801,189,81,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (11,'c_1QC9','p_5AVW',7636,'20181015',3,527901,120,99,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (12,'c_1QC9','p_p_CF',8781,'20181015',9,579501,144,45,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (13,'c_1QC9','p_VK84',8087,'20181015',3,580601,93,192,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (14,'c_1QC9','p_pVS7',2888,'20181015',6,603101,516,306,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (15,'c_1QC9','p_pPK5',2627,'20181015',9,643301,450,684,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (16,'c_1QC9','p_pXFV',7993,'20181015',1,644401,94,9,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (17,'c_1QC9','p_WGQI',7113,'20181015',4,670701,212,180,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (18,'c_1QC9','p_0TMX',3589,'20181015',6,700501,192,450,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (19,'c_1QC9','p_pCUR',4287,'20181015',7,710301,595,350,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (20,'c_1QC9','p_p_EA',9074,'20181015',7,780001,497,413,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (21,'c_1QC9','p_pO5B',976,'20181015',7,781501,56,119,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (22,'c_1QC9','p_XT7N',255,'20181015',7,794601,35,455,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (23,'c_1QC9','p_24K2',8933,'20181015',4,948701,256,176,797,797);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (1,'c_2808','p_pM52',4914,'20181015',3,46702,159,186,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (2,'c_2808','p_L3XE',1859,'20181015',3,107602,87,15,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (3,'c_2808','p_p_KU',1798,'20181015',2,203002,44,144,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (4,'c_2808','p_p67E',864,'20181015',8,208802,224,328,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (5,'c_2808','p_62VG',5864,'20181015',3,213402,252,189,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (6,'c_2808','p_pTW3',6400,'20181015',9,277102,450,765,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (7,'c_2808','p_p_EA',9074,'20181015',7,280002,497,413,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (8,'c_2808','p_50K1',1891,'20181015',1,298102,61,57,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (9,'c_2808','p_p_C1',3209,'20181015',9,330102,441,801,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (10,'c_2808','p_JG6X',9127,'20181015',5,333602,200,450,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (11,'c_2808','p_p_FD',5509,'20181015',5,337402,250,355,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (12,'c_2808','p_pH67',4868,'20181015',5,446402,450,360,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (13,'c_2808','p_p_1H',1116,'20181015',6,490802,192,354,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (14,'c_2808','p_p9H4',3911,'20181015',3,492102,57,168,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (15,'c_2808','p_pJ8Q',9640,'20181015',9,496002,45,450,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (16,'c_2808','p_pVPD',9592,'20181015',8,523202,744,472,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (17,'c_2808','p_pXFV',7993,'20181015',4,564402,376,36,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (18,'c_2808','p_pBY6',5554,'20181015',9,567302,45,504,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (19,'c_2808','p_ZCSN',9899,'20181015',1,607502,13,92,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (20,'c_2808','p_p_1H',1116,'20181015',9,620802,288,531,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (21,'c_2808','p_H76C',9237,'20181015',5,738202,495,430,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (22,'c_2808','p_p_EX',1760,'20181015',9,792402,603,648,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (23,'c_2808','p_p_1H',1116,'20181015',2,860802,64,118,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (24,'c_2808','p_p_27',7992,'20181015',8,873902,336,360,743,743);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (1,'c_2CAD','p_VH0J',7559,'20181015',5,106103,200,185,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (2,'c_2CAD','p_p_CF',8781,'20181015',6,149503,96,30,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (3,'c_2CAD','p_pIC3',7256,'20181015',2,162803,126,54,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (4,'c_2CAD','p_p8UB',2730,'20181015',2,182203,92,60,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (5,'c_2CAD','p_pOWW',9028,'20181015',2,189803,26,58,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (6,'c_2CAD','p_p3J0',7156,'20181015',6,237003,180,204,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (7,'c_2CAD','p_p_KN',3885,'20181015',7,248403,469,413,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (8,'c_2CAD','p_HHML',5460,'20181015',6,251803,498,336,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (9,'c_2CAD','p_ZNT7',7009,'20181015',7,252603,315,21,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (10,'c_2CAD','p_pYV7',8398,'20181015',5,260903,160,240,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (11,'c_2CAD','p_LKLO',7552,'20181015',3,289003,192,138,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (12,'c_2CAD','p_VK84',8087,'20181015',8,290603,248,512,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (13,'c_2CAD','p_pGJS',2718,'20181015',8,321403,784,632,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (14,'c_2CAD','p_pGV7',9243,'20181015',6,386603,402,30,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (15,'c_2CAD','p_pM4Q',3479,'20181015',7,501203,315,21,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (16,'c_2CAD','p_R6U9',5905,'20181015',8,502503,472,224,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (17,'c_2CAD','p_p8UB',2730,'20181015',4,572203,184,120,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (18,'c_2CAD','p_p_JY',6736,'20181015',8,605703,520,592,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (19,'c_2CAD','p_p9H4',3911,'20181015',1,612103,19,56,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (20,'c_2CAD','p_XT7N',255,'20181015',6,634603,30,390,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (21,'c_2CAD','p_p_EA',9074,'20181015',5,640003,355,295,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (22,'c_2CAD','p_p_07',708,'20181015',7,669903,175,441,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (23,'c_2CAD','p_WGQI',7113,'20181015',8,670703,424,360,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (24,'c_2CAD','p_FVWR',7672,'20181015',4,705503,64,212,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (25,'c_2CAD','p_L3XE',1859,'20181015',8,717603,232,40,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (26,'c_2CAD','p_pBS6',3020,'20181015',8,744903,384,728,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (27,'c_2CAD','p_p_6O',6129,'20181015',1,774003,80,38,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (28,'c_2CAD','p_pNNC',9000,'20181015',8,824203,608,384,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (29,'c_2CAD','p_ZCSN',9899,'20181015',1,847503,13,92,408,408);
Insert into cumming (RN,CUST_ID,ITEM,PRICE,PURCHASE_DATE,QTY,SEQ,SUM_VOLUME,SUM_WEIGHT,UPPER_LIMIT_VOLUME,UPPER_LIMIT_WEIGHT) values (30,'c_2CAD','p_p_JZ',3914,'20181015',5,944803,490,80,408,408);
commit;

顧客ごとに用意したダンボールの総数のうち、各ダンボールは何個目にあたるかを表現してみる。

rev.sql

SELECT
    cust_id
    , grp
    , item
    ,DENSE_RANK() OVER (PARTITION BY cust_id ORDER BY cust_id,grp) AS mole
    ,COUNT(DISTINCT grp) OVER (PARTITION BY cust_id) AS deno
FROM
    cumming
    MATCH_RECOGNIZE(
                    ORDER BY cust_id,rn
                    MEASURES
                        match_number() AS grp--パターン一致ごとに、見つかった順序で1から始まる連番が返されます。
                        ,classifier() AS ptn--特定の行に適用されているパターン全体の変数名が返されます。
                    ALL ROWS PER MATCH--複数行にわたる一致で、一致の行ごとに1つの出力行が生成されます。
                    PATTERN (alone_exceeded | cume_exceeded+ )--正規表現らしい。。alone_exceededまたはrecurrenceが1回以上の繰り返し
                    DEFINE alone_exceeded AS  cust_id = prev(cust_id) AND sum_volume > upper_limit_volume OR sum_weight > upper_limit_weight--単独超過の場合
                    , cume_exceeded AS cust_id = prev(cust_id) AND SUM(sum_volume) <= upper_limit_volume AND SUM(sum_weight) <= upper_limit_weight--累積加算超過の場合(pattern句の「+」と相まって漸化的に加算されていくぽい)
                    )
;


image.png

image.png

image.png

分母分子っていうかんじがしますね。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0