まえがき
付番のやり方をいろいろ調べていて、tipsとして簡単にupしておこうとおもいます。
参考文献
データ準備
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
;
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
;
まとめ
型はこんな感じだとおもう。
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句の「+」と相まって漸化的に加算されていくぽい)
)
;
分母分子っていうかんじがしますね。