聚合ecomomy

with economy as (SELECT currency_change ,currency_type ,currency_value ,kingdom_id , mid,platform_id ,player_id ,timestamp,format_timestamp('%Y-%m-%d %H', timestamp )AS hourtFROM `heidao-market.mafia1.economy_data`WHERE DATE(timestamp ) >"2019-07-01" and currency_type in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22)and player_id in ( SELECT distinct playerID as player_id FROM `heidao-market.analysis.purchase_records` where money_value_in_total>5000))SELECTSUM(currency_value ) AS cv,player_id,hourt,currency_type ,SUM(currency_change ) AS sum_change,AVG(currency_value ) AS m_left,SUM(currency_change )/AVG(currency_value +1.1234567) AS ratio,COUNTIF( currency_change <=0) AS ct_m,COUNTIF(currency_change >0) AS ct_p,COUNTIF( currency_type <=0)/( COUNTIF( currency_value <=0)+COUNTIF(currency_value >0)) as m_p,max( currency_change ) as max_change,min(currency_change ) as min_change,max( currency_type )/( min( currency_value )+1.1234567) as mm_ratio,min(timestamp) as mtimeFROM economyGROUP BY player_id,hourt ,currency_type

聚合offer_purchase

--SELECT * FROM `heidao-market.mafia.offer_purchase` WHERE DATE(offer_time) = "2019-08-20" LIMIT 1000WITH ps AS (SELECT DATE(timestamp) AS dt, pay_amount , exchange , player_id,platform_id,format_timestamp('%Y-%m-%d %H', timestamp )AS hourtFROM `heidao-market.mafia1.offer_purchase`where 　 DATE(timestamp ) > "2019-07-01" and player_id in (SELECT distinct playerID as player_id FROM `heidao-market.analysis.purchase_records` where money_value_in_total>5000)) SELECTsum(pay_amount* exchange) as sum_money ,player_id,hourt FROM psGROUP BY player_id,hourt

聚合army

WITH ps AS (SELECT DATE(timestamp )AS dt, army_num, mid, army_id, left_num, kingdom_id,timestamp , player_id,platform_id,format_timestamp('%Y-%m-%d %H', timestamp )AS hourtFROM `heidao-market.mafia1.army`where 　 DATE(timestamp ) >"2019-09-01" and player_id in (SELECT distinct playerID as player_id FROM `heidao-market.analysis.purchase_records` where money_value_in_total>5000))SELECTSUM(army_num) AS sum_change,AVG(left_num ) AS m_left,SUM(army_num)/AVG(left_num +1.1234567) AS ratio,COUNTIF( army_num<=0) AS ct_m,COUNTIF(army_num>0) AS ct_p,COUNTIF( army_num<=0)/( COUNTIF( army_num<=0)+COUNTIF(army_num>0)) as m_p,max( army_num) as max_change,min( army_num) as min_change,max( army_num)/( min( army_num)+1.1234567) as mm_ratio,player_id,hourt FROM psGROUP BY player_id,hourt

總結(jié)

以上是生活随笔為你收集整理的bigquery按照小时来聚合数据的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。