CREATE TABLE IF NOT EXISTS ecommerce_behavior
(
    event_time    STRING,
    event_type    STRING,
    product_id    BIGINT,
    category_id   BIGINT,
    category_code STRING,
    brand         STRING,
    price         DOUBLE,
    user_id       BIGINT,
    user_session  STRING
) 
PARTITIONED BY
(
    dt            STRING
);

odpscmd -e "tunnel u -cf true -h true -acp true  2020-Apr.csv ecommerce_behavior/dt=202004;"

INSERT OVERWRITE TABLE ecommerce_behavior_table PARTITION(dt)
SELECT *, DATEPART(event_time,'hh') ts_hour, WEEKDAY(event_time) ts_weekday,
    TO_CHAR(event_time, 'yyyymmdd') as dt
FROM (
    SELECT DATETIME(replace(event_time, ' UTC', '')) as event_time,
        event_type, product_id, category_id, category_code, brand, price,
        user_id, user_session, split(category_code, '\\.')[0] cat_0,
        split(category_code, '\\.')[1] cat_1,
        split(category_code, '\\.')[2] cat_2,
        split(category_code, '\\.')[3] cat_3
    FROM ecommerce_behavior
    WHERE dt > 0
);

CREATE TABLE IF NOT EXISTS category_price_info
(
    category_id BIGINT COMMENT '商品类目ID',
    avg_price DOUBLE COMMENT '当前类目下的平均价格',
    std_price DOUBLE COMMENT '当前类目下价格的标准差'
)
PARTITIONED BY (dt STRING COMMENT '日期')
;

INSERT OVERWRITE TABLE category_price_info PARTITION(dt)
SELECT category_id, AVG(price) avg_price, STDDEV_SAMP(price) std_price, dt
FROM ecommerce_item_table
WHERE dt>0 AND category_id IS NOT NULL AND price IS NOT NULL
GROUP BY dt, category_id;

-- 请查看`ecommerce_behavior_wide_table`表的生成代码
select if(std_price=0.0, 0.0, (price-avg_price)/std_price) as norm_price, ...

CREATE VIEW IF NOT EXISTS price_level_lookup_table AS
SELECT product_id, category_id, MIN(price_level) price_level
FROM (
    SELECT product_id, category_id, 
        NTILE(10) OVER(PARTITION BY category_id ORDER BY price)-1 AS price_level
    FROM (
        SELECT DISTINCT product_id, category_id, price
        FROM ecommerce_item_table
        WHERE dt > 0
    )
)
GROUP BY product_id, category_id;

CREATE VIEW IF NOT EXISTS category_lookup_table AS
SELECT category_id, ROW_NUMBER() OVER() - 1 AS category_idx
FROM (
    SELECT DISTINCT category_id
    FROM ecommerce_item_table
    WHERE dt > 0
);

-- 完整代码请查看 `ecommerce_behavior_view` 试图的生成代码
SELECT
    CASE 
        WHEN ts_hour<=3 THEN 0
        WHEN ts_hour<=7 THEN 1
        WHEN ts_hour<=14 THEN 2
        WHEN ts_hour<=20 THEN 3
        ELSE 4 
    END AS hour_bin, ...
SELECT CONCAT(ts_weekday, '_', hour_bin) as weekday_hour

-- 完整代码请查看 `ecommerce_behavior_view` 试图的生成代码
SELECT `(rn)?+.+`
FROM (
    SELECT ...,
        ROW_NUMBER() OVER (PARTITION BY user_id, user_session, product_id, event_type ORDER BY event_time DESC) rn
    FROM ecommerce_behavior_table
    WHERE dt>=@bizdate
)
WHERE rn=1 -- drop_duplicates

CREATE VIEW IF NOT EXISTS ecommerce_session_purchase(@bizdate STRING)
AS
SELECT NVL(user_session, 'UNKNOWN') user_session, user_id, product_id,
    MAX(IF(event_type='purchase', 1, 0)) is_purchased -- label
FROM ecommerce_behavior_table
WHERE dt>=@bizdate
GROUP BY NVL(user_session, 'UNKNOWN'), user_id, product_id
;

INSERT OVERWRITE TABLE ecommerce_behavior_wide_table PARTITION(dt)
SELECT is_purchased, event_time, event_type, A.product_id, category_id, category_code, brand, price, A.user_id,
  A.user_session, cat_0, cat_1, cat_2, ts_hour, ts_weekday, hour_bin, weekday_hour, product_idx, category_idx,
  brand_idx, cat_0_idx, cat_1_idx, cat_2_idx, code_idx, price_level,
  SUM(IF(event_type IN ("purchase", "cart"), 1, 0)) OVER (PARTITION BY A.user_session ORDER BY event_time) as activity_count,
  if(std_price=0.0, 0.0, (price-avg_price)/std_price) as norm_price,
  A.dt
FROM ecommerce_behavior_view('0') A
JOIN ecommerce_session_purchase('0') B
ON A.dt=B.dt AND A.user_session = B.user_session AND A.product_id=B.product_id AND A.user_id=B.user_id
;

CREATE TABLE IF NOT EXISTS ecommerce_item_count_daily
(
    field_idx   BIGINT,
    event_type  STRING,
    total_cnt   BIGINT,
    positive_cnt BIGINT,
    probs       DOUBLE
)
COMMENT 'item field counting'
PARTITIONED BY (field STRING COMMENT 'field name', dt STRING COMMENT 'yyyymmdd')
;

FROM (
    SELECT *
    FROM ecommerce_behavior_wide_table
    WHERE dt > 0 AND event_type IN ('purchase', 'cart', 'view')
)
INSERT OVERWRITE TABLE ecommerce_item_count_daily PARTITION(field='product', dt)
SELECT product_idx, event_type, COUNT(1) cnt, SUM(is_purchased) positive_cnt, AVG(is_purchased) probs, dt
GROUP BY product_idx, event_type, dt

INSERT OVERWRITE TABLE ecommerce_item_count_daily PARTITION(field='price_level', dt)
SELECT price_level, event_type, COUNT(1) cnt, SUM(is_purchased) positive_cnt, AVG(is_purchased) probs, dt
GROUP BY price_level, event_type, dt

INSERT OVERWRITE TABLE ecommerce_item_count_daily PARTITION(field='cate_price_level', dt)
SELECT category_idx*10+price_level, event_type, COUNT(1) cnt, SUM(is_purchased) positive_cnt, AVG(is_purchased) probs, dt
GROUP BY category_idx, price_level, event_type, dt

INSERT OVERWRITE TABLE ecommerce_item_count_daily PARTITION(field='category', dt)
SELECT category_idx, event_type, COUNT(1) cnt, SUM(is_purchased) positive_cnt, AVG(is_purchased) probs, dt
GROUP BY category_idx, event_type, dt

......

INSERT OVERWRITE TABLE ecommerce_item_cumulate_count PARTITION(field, dt)
SELECT field_idx, event_type, 
    total_cnt_1d, pos_cnt_1d, total_cnt_1d-pos_cnt_1d AS neg_cnt_1d, 
    SUM(pos_cnt_1d) OVER(PARTITION BY dt, field, event_type) global_pos_cnt_1d,
    SUM(total_cnt_1d-pos_cnt_1d) OVER(PARTITION BY dt, field, event_type) global_neg_cnt_1d,
    total_cnt_3d, pos_cnt_3d, total_cnt_3d-pos_cnt_3d AS neg_cnt_3d, 
    SUM(pos_cnt_3d) OVER(PARTITION BY dt, field, event_type) global_pos_cnt_3d,
    SUM(total_cnt_3d-pos_cnt_3d) OVER(PARTITION BY dt, field, event_type) global_neg_cnt_3d,
    total_cnt_7d, pos_cnt_7d, total_cnt_7d-pos_cnt_7d AS neg_cnt_7d, 
    SUM(pos_cnt_7d) OVER(PARTITION BY dt, field, event_type) global_pos_cnt_7d,
    SUM(total_cnt_7d-pos_cnt_7d) OVER(PARTITION BY dt, field, event_type) global_neg_cnt_7d,
    total_cnt_30d, pos_cnt_30d, total_cnt_30d-pos_cnt_30d AS neg_cnt_30d, 
    SUM(pos_cnt_30d) OVER(PARTITION BY dt, field, event_type) global_pos_cnt_30d,
    SUM(total_cnt_30d-pos_cnt_30d) OVER(PARTITION BY dt, field, event_type) global_neg_cnt_30d,
    field, dt
FROM (
    SELECT field_idx, event_type, total_cnt as total_cnt_1d, positive_cnt as pos_cnt_1d,
        SUM(total_cnt) OVER(PARTITION BY field, field_idx, event_type ORDER BY dt ASC ROWS 2 PRECEDING) total_cnt_3d,
        SUM(positive_cnt) OVER(PARTITION BY field, field_idx, event_type ORDER BY dt ASC ROWS 2 PRECEDING) pos_cnt_3d,
        SUM(total_cnt) OVER(PARTITION BY field, field_idx, event_type ORDER BY dt ASC ROWS 6 PRECEDING) total_cnt_7d,
        SUM(positive_cnt) OVER(PARTITION BY field, field_idx, event_type ORDER BY dt ASC ROWS 6 PRECEDING) pos_cnt_7d,
        SUM(total_cnt) OVER(PARTITION BY field, field_idx, event_type ORDER BY dt ASC ROWS 29 PRECEDING) total_cnt_30d,
        SUM(positive_cnt) OVER(PARTITION BY field, field_idx, event_type ORDER BY dt ASC ROWS 29 PRECEDING) pos_cnt_30d,
        field, dt
    FROM (
        SELECT field, event_type, field_idx, TO_CHAR(DATEADD(TO_DATE(dt, 'yyyymmdd'), delta, 'dd'), 'yyyymmdd') dt,
            SUM(BIGINT(NVL(total_cnt, 0))) total_cnt, SUM(BIGINT(NVL(positive_cnt, 0))) positive_cnt
        FROM (
            SELECT TRANS_ARRAY(4, ',', dt, field, event_type, field_idx, string(total_cnt), string(positive_cnt), range(0, 30))
                AS (dt, field, event_type, field_idx, total_cnt, positive_cnt, delta)
            FROM ecommerce_item_count_daily
            WHERE dt>0 AND field NOT IN ('weekday_hour', 'product') AND event_type != 'purchase'
        )
        GROUP BY TO_CHAR(DATEADD(TO_DATE(dt, 'yyyymmdd'), delta, 'dd'), 'yyyymmdd'), field, event_type, field_idx
    )
)
WHERE dt<'20200501';

INSERT OVERWRITE TABLE ecommerce_user_bin_count_v2 PARTITION(field, dt)
SELECT 30d.user_id, 30d.field_idx, 30d.event_type, 
    nvl(pos_ntile_1d, 9), nvl(neg_ntile_1d, 9), nvl(pos_ntile_3d, 9), nvl(neg_ntile_3d, 9),
    nvl(pos_ntile_7d, 9), nvl(neg_ntile_7d, 9), nvl(pos_ntile_30d, 9), nvl(neg_ntile_30d, 9),
    nvl(pos_gauss_rank_1d, 0), nvl(neg_gauss_rank_1d, 0), nvl(pos_gauss_rank_3d, 0), nvl(neg_gauss_rank_3d, 0),
    nvl(pos_gauss_rank_7d, 0), nvl(neg_gauss_rank_7d, 0), nvl(pos_gauss_rank_30d, 0), nvl(neg_gauss_rank_30d, 0),
    nvl(woe_1d, 0), nvl(woe_3d, 0), nvl(woe_7d, 0), nvl(woe_30d, 0), 
    nvl(odds_1d, 0), nvl(odds_3d, 0), nvl(odds_7d, 0), nvl(odds_30d, 0),
    nvl(odds_ratio_1d, 0), nvl(odds_ratio_3d, 0), nvl(odds_ratio_7d, 0), nvl(odds_ratio_30d, 0),
    30d.field, 30d.dt
FROM (
    SELECT dt, field, user_id, field_idx, event_type, 
        pos_ntile_30d, neg_ntile_30d,
        GAUSS_RANK(pos_rank_30d) pos_gauss_rank_30d,
        GAUSS_RANK(neg_rank_30d) neg_gauss_rank_30d,
        LN(1e-6+pos_ratio_30d/(1e-6+neg_ratio_30d)) AS woe_30d,
        odds_30d, odds_ratio_30d
    FROM (
        SELECT dt, user_id, field, field_idx, event_type,
            NTILE(10) OVER(PARTITION BY dt, user_id, field, event_type ORDER BY pos_cnt_30d DESC)-1 AS pos_ntile_30d,
            NTILE(10) OVER(PARTITION BY dt, user_id, field, event_type ORDER BY neg_cnt_30d DESC)-1 AS neg_ntile_30d,
            PERCENT_RANK() OVER(PARTITION BY dt, user_id, field, event_type ORDER BY pos_cnt_30d DESC) AS pos_rank_30d,
            PERCENT_RANK() OVER(PARTITION BY dt, user_id, field, event_type ORDER BY neg_cnt_30d DESC) AS neg_rank_30d,
            if(global_pos_cnt_30d=0, 0, pos_cnt_30d/global_pos_cnt_30d) AS pos_ratio_30d,
            if(global_neg_cnt_30d=0, 0, neg_cnt_30d/global_neg_cnt_30d) AS neg_ratio_30d,
            LN(1e-6 + pos_cnt_30d)-LN(1e-6 + neg_cnt_30d) AS odds_30d,
            LN(1e-6+pos_cnt_30d)-LN(1e-6+neg_cnt_30d)-LN(1e-6+global_pos_cnt_30d-pos_cnt_30d)+LN(1e-6+global_neg_cnt_30d-neg_cnt_30d) AS odds_ratio_30d
        FROM ecommerce_user_cumulate_count
        where dt>='20191031' AND total_cnt_30d>0
    )
) 30d
LEFT JOIN (
    SELECT dt, field, user_id, field_idx, event_type, 
        pos_ntile_7d, neg_ntile_7d,
        GAUSS_RANK(pos_rank_7d) pos_gauss_rank_7d,
        GAUSS_RANK(neg_rank_7d) neg_gauss_rank_7d,
        LN(1e-6+pos_ratio_7d/(1e-6+neg_ratio_7d)) AS woe_7d,
        odds_7d, odds_ratio_7d
    FROM (
        SELECT dt, user_id, field, field_idx, event_type,
            NTILE(10) OVER(PARTITION BY dt, user_id, field, event_type ORDER BY pos_cnt_7d DESC)-1 AS pos_ntile_7d,
            NTILE(10) OVER(PARTITION BY dt, user_id, field, event_type ORDER BY neg_cnt_7d DESC)-1 AS neg_ntile_7d,
            PERCENT_RANK() OVER(PARTITION BY dt, user_id, field, event_type ORDER BY pos_cnt_7d DESC) AS pos_rank_7d,
            PERCENT_RANK() OVER(PARTITION BY dt, user_id, field, event_type ORDER BY neg_cnt_7d DESC) AS neg_rank_7d,
            if(global_pos_cnt_7d=0, 0, pos_cnt_7d/global_pos_cnt_7d) AS pos_ratio_7d,
            if(global_neg_cnt_7d=0, 0, neg_cnt_7d/global_neg_cnt_7d) AS neg_ratio_7d,
            LN(1e-6 + pos_cnt_7d) - LN(1e-6 + neg_cnt_7d) AS odds_7d,
            LN(1e-6+pos_cnt_7d)-LN(1e-6+neg_cnt_7d)-LN(1e-6+global_pos_cnt_7d-pos_cnt_7d)+LN(1e-6+global_neg_cnt_7d-neg_cnt_7d) AS odds_ratio_7d
        FROM ecommerce_user_cumulate_count
        where dt>='20191031' AND total_cnt_7d>0
    )
) 7d
ON 30d.dt=7d.dt AND 30d.field=7d.field AND 30d.user_id=7d.user_id AND 30d.event_type=7d.event_type AND 30d.field_idx=7d.field_idx
LEFT JOIN (
    SELECT dt, field, user_id, field_idx, event_type, 
        pos_ntile_3d, neg_ntile_3d,
        GAUSS_RANK(pos_rank_3d) pos_gauss_rank_3d,
        GAUSS_RANK(neg_rank_3d) neg_gauss_rank_3d,
        LN(1e-6+pos_ratio_3d/(1e-6+neg_ratio_3d)) AS woe_3d,
        odds_3d, odds_ratio_3d
    FROM (
        SELECT dt, user_id, field, field_idx, event_type,
            NTILE(10) OVER(PARTITION BY dt, user_id, field, event_type ORDER BY pos_cnt_3d DESC)-1 AS pos_ntile_3d,
            NTILE(10) OVER(PARTITION BY dt, user_id, field, event_type ORDER BY neg_cnt_3d DESC)-1 AS neg_ntile_3d,
            PERCENT_RANK() OVER(PARTITION BY dt, user_id, field, event_type ORDER BY pos_cnt_3d DESC) AS pos_rank_3d,
            PERCENT_RANK() OVER(PARTITION BY dt, user_id, field, event_type ORDER BY neg_cnt_3d DESC) AS neg_rank_3d,
            if(global_pos_cnt_3d=0, 0, pos_cnt_3d/global_pos_cnt_3d) AS pos_ratio_3d,
            if(global_neg_cnt_3d=0, 0, neg_cnt_3d/global_neg_cnt_3d) AS neg_ratio_3d,
            LN(1e-6 + pos_cnt_3d) - LN(1e-6 + neg_cnt_3d) AS odds_3d,
            LN(1e-6+pos_cnt_3d)-LN(1e-6+neg_cnt_3d)-LN(1e-6+global_pos_cnt_3d-pos_cnt_3d)+LN(1e-6+global_neg_cnt_3d-neg_cnt_3d) AS odds_ratio_3d
        FROM ecommerce_user_cumulate_count
        where dt>='20191031' AND total_cnt_3d>0
    )
) 3d
ON 30d.dt=3d.dt AND 30d.field=3d.field AND 30d.user_id=3d.user_id AND 30d.event_type=3d.event_type AND 30d.field_idx=3d.field_idx
LEFT JOIN (
    SELECT dt, field, user_id, field_idx, event_type, 
        pos_ntile_1d, neg_ntile_1d,
        GAUSS_RANK(pos_rank_1d) pos_gauss_rank_1d,
        GAUSS_RANK(neg_rank_1d) neg_gauss_rank_1d,
        LN(1e-6+pos_ratio_1d/(1e-6+neg_ratio_1d)) AS woe_1d,
        odds_1d, odds_ratio_1d
    FROM (
        SELECT dt, user_id, field, field_idx, event_type,
            NTILE(10) OVER(PARTITION BY dt, user_id, field, event_type ORDER BY pos_cnt_1d DESC)-1 AS pos_ntile_1d,
            NTILE(10) OVER(PARTITION BY dt, user_id, field, event_type ORDER BY neg_cnt_1d DESC)-1 AS neg_ntile_1d,
            PERCENT_RANK() OVER(PARTITION BY dt, user_id, field, event_type ORDER BY pos_cnt_1d DESC) AS pos_rank_1d,
            PERCENT_RANK() OVER(PARTITION BY dt, user_id, field, event_type ORDER BY neg_cnt_1d DESC) AS neg_rank_1d,
            if(global_pos_cnt_1d=0, 0, pos_cnt_1d/global_pos_cnt_1d) AS pos_ratio_1d,
            if(global_neg_cnt_1d=0, 0, neg_cnt_1d/global_neg_cnt_1d) AS neg_ratio_1d,
            LN(1e-6 + pos_cnt_1d) - LN(1e-6 + neg_cnt_1d) AS odds_1d,
            LN(1e-6+pos_cnt_1d)-LN(1e-6+neg_cnt_1d)-LN(1e-6+global_pos_cnt_1d-pos_cnt_1d)+LN(1e-6+global_neg_cnt_1d-neg_cnt_1d) AS odds_ratio_1d
        FROM ecommerce_user_cumulate_count
        where dt>='20191031' AND total_cnt_1d>0
    )
) 1d
ON 30d.dt=1d.dt AND 30d.field=1d.field AND 30d.user_id=1d.user_id AND 30d.event_type=1d.event_type AND 30d.field_idx=1d.field_idx
;