da50c_calc

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_01
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_02
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_03
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_04
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_05
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_06
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_07
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_08
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_09
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_10
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_11
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_12
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_13
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_14
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_15
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_16
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_17
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_18
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_19
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_20
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_21
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_22
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_23
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_24
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_25
;

 DROP TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_26
;

 DROP TABLE CITY_EVENT_DATA.D_50C_METRO_CALC_S_NGPT_DATA
;

 SET hivevar:START_DAY_01='2018-08-24'
;

 SET hivevar:END_DAY_01='2018-08-25'
;

 SET hivevar:START_DAY_02='2018-08-31'
;

 SET hivevar:END_DAY_02='2018-09-01'
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_01
AS
SELECT nm
,line
,MIN(check_day) AS min_day FROM SANDBOX.YURBASOV_D_50B_METRO_CALC_S_NGPT_DATA_12 GROUP BY nm, line ORDER BY nm, line
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_02
AS
SELECT nm
,line
,route
,ticket_code
,cnt
,check_day
,CASE WHEN nm IN ('Мичуринский проспект','Озёрная','Говорово','Солнцево','Боровское шоссе','Новопеределкино','Рассказовка') THEN 1 ELSE 0 END AS is_main
,CASE WHEN nm IN ('Мичуринский проспект','Озёрная','Говорово','Солнцево','Боровское шоссе','Новопеределкино','Рассказовка') THEN 0 ELSE 1 END AS is_extra FROM SANDBOX.YURBASOV_D_50B_METRO_CALC_S_NGPT_DATA_12
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_03
AS
SELECT ticket_code, check_day
,MAX(is_main) AS main_flg
,MAX(is_extra) AS extra_flg FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_02 GROUP BY ticket_code, check_day
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_04
AS
SELECT ticket_code AS x_ticket_code
,main_flg AS main_flg_a
,extra_flg AS extra_flg_a FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_03 WHERE check_day < ${hivevar:START_DAY_02}
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_05
AS
SELECT ticket_code AS y_ticket_code
,main_flg AS main_flg_b
,extra_flg AS extra_flg_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_03 WHERE check_day >= ${hivevar:START_DAY_02}
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_06
AS
SELECT ticket_code
,CASE WHEN extra_flg_a + main_flg_a > 0 THEN 1 ELSE 0 END AS is_old
,CASE WHEN extra_flg_a + main_flg_a <= 0 THEN 1 ELSE 0 END AS is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_03 JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_04 ON x_ticket_code = ticket_code JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_05 ON y_ticket_code = ticket_code
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_07
AS
SELECT ticket_code AS o_ticket_code
,is_old FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_06 WHERE is_old > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_08
AS
SELECT ticket_code AS n_ticket_code
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_06 WHERE is_new > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_09
AS
SELECT nm
,line
,route
,ticket_code
,cnt
,check_day
,is_old
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM SANDBOX.YURBASOV_D_50B_METRO_CALC_S_NGPT_DATA_12 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_07 ON o_ticket_code = ticket_code LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_08 ON n_ticket_code = ticket_code
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_10
AS
SELECT nm
,line
,route
,ticket_code
,CAST(cnt AS BIGINT) AS cnt
,check_day
,is_old
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_09 WHERE check_day < ${hivevar:START_DAY_02}
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_11
AS
SELECT nm AS nm_11
,line AS line_11
,SUM(cnt) AS total_cnt_a FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_10 GROUP BY nm, line
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_12
AS
SELECT nm
,line
,route
,ticket_code
,CAST(cnt AS BIGINT) AS cnt
,check_day
,is_old
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_09 WHERE check_day >= ${hivevar:START_DAY_02}
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_13
AS
SELECT nm AS nm_13
,line AS line_13
,SUM(cnt) AS total_cnt_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_12 GROUP BY nm, line
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_14
AS
SELECT nm
,line
,route
,ticket_code
,CAST(cnt AS BIGINT) AS cnt
,check_day
,is_old
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_09 WHERE route != 'метро' AND check_day < ${hivevar:START_DAY_02}
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_15
AS
SELECT nm AS nm_15
,line AS line_15
,SUM(cnt) AS ngpt_cnt_a FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_14 GROUP BY nm, line
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_16
AS
SELECT nm
,line
,route
,ticket_code
,CAST(cnt AS BIGINT) AS cnt
,check_day
,is_old
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_09 WHERE route != 'метро' AND check_day >= ${hivevar:START_DAY_02}
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_17
AS
SELECT nm AS nm_17
,line AS line_17
,SUM(cnt) AS ngpt_cnt_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_16 GROUP BY nm, line
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_18
AS
SELECT nm AS nm_18
,line AS line_18
,SUM(cnt) AS new_total_cnt_a FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_10 GROUP BY nm, line, is_new HAVING is_new > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_19
AS
SELECT nm AS nm_19
,line AS line_19
,SUM(cnt) AS new_total_cnt_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_12 GROUP BY nm, line, is_new HAVING is_new > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_20
AS
SELECT nm AS nm_20
,line AS line_20
,SUM(cnt) AS new_ngpt_cnt_a FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_14 GROUP BY nm, line, is_new HAVING is_new > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_21
AS
SELECT nm AS nm_21
,line AS line_21
,SUM(cnt) AS new_ngpt_cnt_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_16 GROUP BY nm, line, is_new HAVING is_new > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_22
AS
SELECT nm AS nm_22
,line AS line_22
,SUM(cnt) AS old_total_cnt_a FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_10 GROUP BY nm, line, is_old HAVING is_old > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_23
AS
SELECT nm AS nm_23
,line AS line_23
,SUM(cnt) AS old_total_cnt_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_12 GROUP BY nm, line, is_old HAVING is_old > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_24
AS
SELECT nm AS nm_24
,line AS line_24
,SUM(cnt) AS old_ngpt_cnt_a FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_14 GROUP BY nm, line, is_old HAVING is_old > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_25
AS
SELECT nm AS nm_25
,line AS line_25
,SUM(cnt) AS old_ngpt_cnt_b FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_16 GROUP BY nm, line, is_old HAVING is_old > 0
;

 CREATE TABLE SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_26
AS
SELECT nm
,line
,CASE WHEN total_cnt_a + total_cnt_b > 0 THEN 1 - total_cnt_b / (total_cnt_a + total_cnt_b) ELSE 0.0 END AS p
,total_cnt_a
,total_cnt_b
,ngpt_cnt_a
,ngpt_cnt_b
        ,CASE WHEN new_total_cnt_a > 0 THEN new_total_cnt_a ELSE 0 END AS new_total_cnt_a
        ,CASE WHEN new_total_cnt_b > 0 THEN new_total_cnt_b ELSE 0 END AS new_total_cnt_b
        ,CASE WHEN new_ngpt_cnt_a > 0 THEN new_ngpt_cnt_a ELSE 0 END AS new_ngpt_cnt_a
        ,CASE WHEN new_ngpt_cnt_b > 0 THEN new_ngpt_cnt_b ELSE 0 END AS new_ngpt_cnt_b
        ,old_total_cnt_a
        ,old_total_cnt_b
        ,old_ngpt_cnt_a
        ,old_ngpt_cnt_b
        ,CASE WHEN new_ngpt_cnt_b + new_ngpt_cnt_a > 0 THEN old_total_cnt_b + new_total_cnt_b - old_total_cnt_a - new_total_cnt_a ELSE old_total_cnt_b - old_total_cnt_a END AS auto_cnt_delta
        ,min_day FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_01 JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_11 ON nm = nm_11 AND line = line_11 JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_13 ON nm = nm_13 AND line = line_13 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_15 ON nm = nm_15 AND line = line_15 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_17 ON nm = nm_17 AND line = line_17 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_18 ON nm = nm_18 AND line = line_18 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_19 ON nm = nm_19 AND line = line_19 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_20 ON nm = nm_20 AND line = line_20 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_21 ON nm = nm_21 AND line = line_21 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_22 ON nm = nm_22 AND line = line_22 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_23 ON nm = nm_23 AND line = line_23 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_24 ON nm = nm_24 AND line = line_24 LEFT JOIN SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_25 ON nm = nm_25 AND line = line_25 ORDER BY p DESC
;

 CREATE TABLE CITY_EVENT_DATA.D_50C_METRO_CALC_S_NGPT_DATA(
`nm`  string
,`line`  string
,`p`  string
,`total_cnt_a`  string
,`total_cnt_b`  string
,`ngpt_cnt_a`  string
,`ngpt_cnt_b`  string
,`new_total_cnt_a`  string
,`new_total_cnt_b`  string
,`new_ngpt_cnt_a`  string
,`new_ngpt_cnt_b`  string
,`old_total_cnt_a`  string
,`old_total_cnt_b`  string
,`old_ngpt_cnt_a`  string
,`old_ngpt_cnt_b`  string
,`auto_cnt_delta`  string
) PARTITIONED BY (`min_day` string) STORED AS ORC tblproperties ("orc.compress"="SNAPPY")
;

 INSERT INTO CITY_EVENT_DATA.D_50C_METRO_CALC_S_NGPT_DATA PARTITION (min_day)
SELECT nm AS nm
,line AS line
,p AS p
,total_cnt_a AS total_cnt_a
,total_cnt_b AS total_cnt_b
,ngpt_cnt_a AS ngpt_cnt_a
,ngpt_cnt_b AS ngpt_cnt_b
,new_total_cnt_a AS new_total_cnt_a
,new_total_cnt_b AS new_total_cnt_b
,new_ngpt_cnt_a AS new_ngpt_cnt_a
,new_ngpt_cnt_b AS new_ngpt_cnt_b
,old_total_cnt_a AS old_total_cnt_a
,old_total_cnt_b AS old_total_cnt_b
,old_ngpt_cnt_a AS old_ngpt_cnt_a
,old_ngpt_cnt_b AS old_ngpt_cnt_b
,auto_cnt_delta AS auto_cnt_delta
,min_day FROM SANDBOX.YURBASOV_D_50C_METRO_CALC_S_NGPT_DATA_26
;

 CREATE TABLE IC_CALC.D_50_METRO_CALC_S_NGPT_DATA(
`nm`  string
,`line`  string
,`p`  string
,`total_cnt_a`  string
,`total_cnt_b`  string
,`ngpt_cnt_a`  string
,`ngpt_cnt_b`  string
,`new_total_cnt_a`  string
,`new_total_cnt_b`  string
,`new_ngpt_cnt_a`  string
,`new_ngpt_cnt_b`  string
,`old_total_cnt_a`  string
,`old_total_cnt_b`  string
,`old_ngpt_cnt_a`  string
,`old_ngpt_cnt_b`  string
,`auto_cnt_delta`  string
) PARTITIONED BY (`min_day` string) STORED AS ORC tblproperties ("orc.compress"="SNAPPY")
;

 INSERT INTO IC_CALC.D_50_METRO_CALC_S_NGPT_DATA PARTITION (min_day)
WITH
t_01 AS
(
SELECT nm
,line
,MIN(check_day) AS min_day FROM SANDBOX.YURBASOV_D_50B_METRO_CALC_S_NGPT_DATA_12 GROUP BY nm, line ORDER BY nm, line
),
t_02 AS
(
SELECT nm
,line
,route
,ticket_code
,cnt
,check_day
,CASE WHEN nm IN ('Мичуринский проспект','Озёрная','Говорово','Солнцево','Боровское шоссе','Новопеределкино','Рассказовка') THEN 1 ELSE 0 END AS is_main
,CASE WHEN nm IN ('Мичуринский проспект','Озёрная','Говорово','Солнцево','Боровское шоссе','Новопеределкино','Рассказовка') THEN 0 ELSE 1 END AS is_extra FROM SANDBOX.YURBASOV_D_50B_METRO_CALC_S_NGPT_DATA_12
),
t_03 AS
(
SELECT ticket_code
,MAX(is_main) AS main_flg
,MAX(is_extra) AS extra_flg FROM t_02 GROUP BY ticket_code, check_day
),
t_04 AS
(
SELECT ticket_code AS x_ticket_code
,main_flg AS main_flg_a
,extra_flg AS extra_flg_a FROM t_03 WHERE check_day < ${hivevar:START_DAY_02}
),
t_05 AS
(
SELECT ticket_code AS y_ticket_code
,main_flg AS main_flg_b
,extra_flg AS extra_flg_b FROM t_03 WHERE check_day >= ${hivevar:START_DAY_02}
),
t_06 AS
(
SELECT ticket_code
,CASE WHEN extra_val_a + main_val_a > 0 THEN 1 ELSE 0 END AS is_old
,CASE WHEN extra_val_a + main_val_a <= 0 THEN 1 ELSE 0 END AS is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM t_03 JOIN t_04 ON x_ticket_code = ticket_code JOIN t_05 ON y_ticket_code = ticket_code
),
t_07 AS
(
SELECT ticket_code AS o_ticket_code
,is_old FROM t_06 WHERE is_old > 0
),
t_08 AS
(
SELECT ticket_code AS n_ticket_code
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM t_06 WHERE is_new > 0
),
t_09 AS
(
SELECT nm
,line
,route
,ticket_code
,cnt
,check_day
,is_old
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM SANDBOX.YURBASOV_D_50B_METRO_CALC_S_NGPT_DATA_12 LEFT JOIN t_07 ON o_ticket_code = ticket_code LEFT JOIN t_08 ON n_ticket_code = ticket_code
),
t_10 AS
(
SELECT nm
,line
,route
,ticket_code
,CAST(cnt AS BIGINT) AS cnt
,check_day
,is_old
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM t_09 WHERE check_day < ${hivevar:START_DAY_02}
),
t_11 AS
(
SELECT nm AS nm_11
,line AS nm_11
,SUM(cnt) AS total_cnt_a FROM t_10 GROUP BY nm, line
),
t_12 AS
(
SELECT nm
,line
,route
,ticket_code
,CAST(cnt AS BIGINT) AS cnt
,check_day
,is_old
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM t_09 WHERE check_day >= ${hivevar:START_DAY_02}
),
t_13 AS
(
SELECT nm AS nm_13
,line AS line_13
,SUM(cnt) AS total_cnt_b FROM t_12 GROUP BY nm, line
),
t_14 AS
(
SELECT nm
,line
,route
,ticket_code
,CAST(cnt AS BIGINT) AS cnt
,check_day
,is_old
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM t_09 WHERE route != 'метро' AND check_day < ${hivevar:START_DAY_02}
),
t_15 AS
(
SELECT nm AS nm_15
,line AS line_15
,SUM(cnt) AS ngpt_cnt_a FROM t_14 GROUP BY nm, line
),
t_16 AS
(
SELECT nm
,line
,route
,ticket_code
,CAST(cnt AS BIGINT) AS cnt
,check_day
,is_old
,is_new
,main_flg_a
,main_flg_b
,extra_flg_a
,extra_flg_b FROM t_09 WHERE route != 'метро' AND check_day >= ${hivevar:START_DAY_02}
),
t_17 AS
(
SELECT nm AS nm_17
,line AS line_17
,SUM(cnt) AS ngpt_cnt_b FROM t_16 GROUP BY nm, line
),
t_18 AS
(
SELECT nm AS nm_18
,line AS line_18
,SUM(cnt) AS new_total_cnt_a FROM t_10 GROUP BY nm, line HAVING is_new > 0
),
t_19 AS
(
SELECT nm AS nm_19
,line AS line_19
,SUM(cnt) AS new_total_cnt_b FROM t_12 GROUP BY nm, line HAVING is_new > 0
),
t_20 AS
(
SELECT nm AS nm_20
,line AS line_20
,SUM(cnt) AS new_ngpt_cnt_a FROM t_14 GROUP BY nm, line HAVING is_new > 0
),
t_21 AS
(
SELECT nm AS nm_21
,line AS line_21
,SUM(cnt) AS new_ngpt_cnt_b FROM t_16 GROUP BY nm, line HAVING is_new > 0
),
t_22 AS
(
SELECT nm AS nm_22
,line AS line_22
,SUM(cnt) AS new_total_cnt_a FROM t_10 GROUP BY nm, line HAVING is_old > 0
),
t_23 AS
(
SELECT nm AS nm_23
,line AS line_23
,SUM(cnt) AS old_total_cnt_b FROM t_12 GROUP BY nm, line HAVING is_old > 0
),
t_24 AS
(
SELECT nm AS nm_24
,line AS line_24
,SUM(cnt) AS old_ngpt_cnt_a FROM t_14 GROUP BY nm, line HAVING is_old > 0
),
t_25 AS
(
SELECT nm AS nm_25
,line AS line_25
,SUM(cnt) AS old_ngpt_cnt_b FROM t_16 GROUP BY nm, line HAVING is_old > 0
),
t_26 AS
(
SELECT nm
,line
,1 - total_cnt_b / (total_cnt_a + total_cnt_b) AS p
,total_cnt_a
,total_cnt_b
,ngpt_cnt_a
,ngpt_cnt_b
,new_total_cnt_a
,new_total_cnt_b
,new_ngpt_cnt_a
,new_ngpt_cnt_b
,old_total_cnt_a
,old_total_cnt_b
,old_ngpt_cnt_a
,old_ngpt_cnt_b
,old_total_cnt_b + new_total_cnt_b - old_total_cnt_a - new_total_cnt_a AS auto_cnt_delta
,min_day FROM t_01 JOIN t_11 ON nm = nm_11 AND line = line_11 JOIN t_13 ON nm = nm_13 AND line = line_13 JOIN t_15 ON nm = nm_15 AND line = line_15 JOIN t_17 ON nm = nm_17 AND line = line_17 JOIN t_18 ON nm = nm_18 AND line = line_18 JOIN t_19 ON nm = nm_19 AND line = line_19 JOIN t_20 ON nm = nm_20 AND line = line_20 JOIN t_21 ON nm = nm_21 AND line = line_21 JOIN t_22 ON nm = nm_22 AND line = line_22 JOIN t_23 ON nm = nm_23 AND line = line_23 JOIN t_24 ON nm = nm_24 AND line = line_24 JOIN t_25 ON nm = nm_25 AND line = line_25 ORDER BY p DESC
)
SELECT nm AS nm
,line AS line
,p AS p
,total_cnt_a AS total_cnt_a
,total_cnt_b AS total_cnt_b
,ngpt_cnt_a AS ngpt_cnt_a
,ngpt_cnt_b AS ngpt_cnt_b
,new_total_cnt_a AS new_total_cnt_a
,new_total_cnt_b AS new_total_cnt_b
,new_ngpt_cnt_a AS new_ngpt_cnt_a
,new_ngpt_cnt_b AS new_ngpt_cnt_b
,old_total_cnt_a AS old_total_cnt_a
,old_total_cnt_b AS old_total_cnt_b
,old_ngpt_cnt_a AS old_ngpt_cnt_a
,old_ngpt_cnt_b AS old_ngpt_cnt_b
,auto_cnt_delta AS auto_cnt_delta
,min_day FROM t_26
;