添加鏈接描述
接近完成 0602

with--提取比克展開的snapshot表 status as (select * except (activities,pdt_sorted_ids,ori_sorted_ids) from `heidao-market.mafia1_dwh.player_status` where timestamp >= '2020-06-01 00:35:20.458 UTC' and timestamp <= '2020-06-01 11:35:20.458 UTC' ),--提取禮包購買數據，并且對player_id 進行 按照時間 進行partition purchase as ( select player_id, timestamp, CAST(giftbag_id as STRING) as giftbag_id, STRING_AGG(CAST(giftbag_id as STRING), ",") OVER (PARTITION BY player_id ORDER BY timestamp DESC ROWS BETWEEN 0 FOLLOWING AND 9 FOLLOWING) as recent_giftbags, ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY timestamp desc) as rn from `mafia1_ods.game_log_giftbag` where timestamp >='2020-06-01 00:35:20.458 UTC' and timestamp <'2020-06-01 11:35:20.458 UTC'),--提取曝光數據，并且計算在某個窗口停留的時間view_time 和delta_time 是一樣的意思，都是指時間差 data4 as (select * from( select player_id, giftbag_id,timestamp, IFNULL(IFNULL(view_time, delta_time), 1) as view_time from( select player_id, value_1 as giftbag_id, timestamp, action, TIMESTAMP_DIFF(LEAD(timestamp) OVER (PARTITION BY player_id, event_id, value_1 ORDER BY timestamp ASC), timestamp, MILLISECOND)/1000 as view_time, TIMESTAMP_DIFF(LEAD(timestamp) OVER (PARTITION BY player_id ORDER BY timestamp ASC), timestamp, SECOND) as delta_time from `mafia1_ods.sdk_gift_bag_tracking` where timestamp >= '2020-06-01 04:35:20.458 UTC' and timestamp < '2020-06-01 08:35:20.458 UTC' and type = 70) where action = 1) where view_time >= 1 union all (select player_id, giftbag_id, TIMESTAMP_SUB(timestamp, INTERVAL 60 SECOND) AS timestamp, 5 as view_time from purchasewhere timestamp >= '2020-06-01 04:35:20.458 UTC' and timestamp < '2020-06-01 08:35:20.458 UTC')), ###data5 as (select * except(rank, status_time), string_agg(cast(click_id as string), ',') over(partition by player_id order by timestamp rows between 10 preceding and 1 preceding) as recent_view from( select *, row_number() over (partition by player_id, timestamp order by status_time desc) as rank from(select data4.*except (giftbag_id ),CAST( data4.giftbag_id as int64) as click_id,status.* except(player_id, timestamp), status.timestamp as status_time from (data4 left join status on data4.player_id = status.player_id and status.timestamp between TIMESTAMP_SUB(data4.timestamp, INTERVAL 48 HOUR) and data4.timestamp)))where rank = 1),purchase_record as (select player_id, giftbag_id, timestamp from mafia1_ods.game_log_giftbag where date(timestamp) = '2020-06-01')--select * from data5select * from( select data5.*, purchase_record.timestamp as purchase_time, purchase_record.giftbag_id as buy_id, row_number() over(partition by data5.player_id, data5.click_id, data5.timestamp order by purchase_record.timestamp) as rn from(data5 left join purchase_record on data5.player_id = purchase_record.player_id and purchase_record.giftbag_id = data5.click_id and purchase_record.timestamp between data5.timestamp and TIMESTAMP_ADD(data5.timestamp, INTERVAL 2 HOUR)) ) where rn is null or rn = 1 --and buy_id is not null

接近完成 0601

withstatus as (select * from `heidao-market.mafia1_dwh.player_status` where timestamp >= '2020-06-01 00:35:20.458 UTC'),--data4 as (select timestamp ,value_1,player_id from `mafia1_ods.sdk_gift_bag_tracking` where timestamp >= '2020-06-01 05:35:20.458 UTC' -- and type = 70),###purchase as ( select player_id, timestamp, CAST(giftbag_id as STRING) as giftbag_id, STRING_AGG(CAST(giftbag_id as STRING), ",") OVER (PARTITION BY player_id ORDER BY timestamp DESC ROWS BETWEEN 0 FOLLOWING AND 9 FOLLOWING) as recent_giftbags, ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY timestamp desc) as rn from `mafia1_ods.game_log_giftbag` where timestamp >='2020-06-01 00:35:20.458 UTC' and timestamp <'2020-06-01 11:35:20.458 UTC'),data4 as (select * from( select player_id, value_1,timestamp, IFNULL(IFNULL(view_time, delta_time), 1) as view_time from( select player_id, value_1, timestamp, action, TIMESTAMP_DIFF(LEAD(timestamp) OVER (PARTITION BY player_id, event_id, value_1 ORDER BY timestamp ASC), timestamp, MILLISECOND)/1000 as view_time, TIMESTAMP_DIFF(LEAD(timestamp) OVER (PARTITION BY player_id ORDER BY timestamp ASC), timestamp, SECOND) as delta_time from `mafia1_ods.sdk_gift_bag_tracking` where timestamp >= '2020-06-01 04:35:20.458 UTC' and timestamp < '2020-06-01 08:35:20.458 UTC' and type = 70) where action = 1) where view_time >= 1 union all (select player_id, giftbag_id, TIMESTAMP_SUB(timestamp, INTERVAL 60 SECOND) AS timestamp, 5 as view_time from purchasewhere timestamp >= '2020-06-01 04:35:20.458 UTC' and timestamp < '2020-06-01 08:35:20.458 UTC')), ###data5 as ( select * except(rank, status_time), string_agg(cast(value_1 as string), ',') over(partition by player_id order by timestamp rows between 10 preceding and 1 preceding) as recent_view from( select *, row_number() over (partition by player_id, timestamp order by status_time desc) as rank from(select data4.*, status.* except(player_id, timestamp), status.timestamp as status_time from (data4 left join status on data4.player_id = status.player_id and status.timestamp between TIMESTAMP_SUB(data4.timestamp, INTERVAL 48 HOUR) and data4.timestamp)))where rank = 1),purchase_record as (select player_id, giftbag_id, timestamp from mafia1_ods.game_log_giftbag where timestamp >= '2020-06-01')select * from( select data5.*, purchase_record.timestamp as purchase_time, row_number() over(partition by data5.player_id, data5.giftbag_id, data5.timestamp order by purchase_record.timestamp) as rn from(data5 left join purchase_record on data5.player_id = purchase_record.player_id and purchase_record.giftbag_id = data5.giftbag_id and purchase_record.timestamp between data5.timestamp and TIMESTAMP_ADD(data5.timestamp, INTERVAL 2 HOUR)) ) where rn is null or rn = 1

特征中間狀態 0601

withstatus as (select * from `heidao-market.mafia1_dwh.player_status` where timestamp >= '2020-06-01 04:35:20.458 UTC'),data4 as (select timestamp ,value_1,player_id from `mafia1_ods.sdk_gift_bag_tracking` where timestamp >= '2020-06-01 05:35:20.458 UTC'and type = 70),data5 as ( select * except(rank, status_time), string_agg(cast(value_1 as string), ',') over(partition by player_id order by timestamp rows between 10 preceding and 1 preceding) as recent_view from( select *, row_number() over (partition by player_id, timestamp order by status_time desc) as rank from(select data4.*, status.* except(player_id, timestamp), status.timestamp as status_time from (data4 left join status on data4.player_id = status.player_id and status.timestamp between TIMESTAMP_SUB(data4.timestamp, INTERVAL 48 HOUR) and data4.timestamp)))where rank = 1),purchase_record as (select player_id, giftbag_id, timestamp from mafia1_ods.game_log_giftbag where timestamp >= '2020-06-01')select * from( select data5.*, purchase_record.timestamp as purchase_time, row_number() over(partition by data5.player_id, data5.giftbag_id, data5.timestamp order by purchase_record.timestamp) as rn from(data5 left join purchase_record on data5.player_id = purchase_record.player_id and purchase_record.giftbag_id = data5.giftbag_id and purchase_record.timestamp between data5.timestamp and TIMESTAMP_ADD(data5.timestamp, INTERVAL 2 HOUR)) ) where rn is null or rn = 1 withstatus as (select * from `mafia1_ods.game_log_status_snapshot` where timestamp >= '2020-05-28 06:35:20.458 UTC'),data4 as (select timestamp ,value_1,player_id from `mafia1_ods.sdk_gift_bag_tracking` where timestamp >= '2020-05-28 10:35:20.458 UTC'and type = 70)select * except(rank, status_time), string_agg(cast(value_1 as string), ',') over(partition by player_id order by timestamp rows between 10 preceding and 1 preceding) as recent_view from( select *, row_number() over (partition by player_id, timestamp order by status_time desc) as rank from(select data4.*, status.* except(player_id, timestamp), status.timestamp as status_time from (data4 left join status on data4.player_id = status.player_id and status.timestamp between TIMESTAMP_SUB(data4.timestamp, INTERVAL 48 HOUR) and data4.timestamp)))where rank = 1

總結

以上是生活随笔為你收集整理的sql 2020 0528的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。