在sql中做去重是一件麻烦的事,一般情况下只能通过group by 所有字段来做去重。
现在考虑一个简单的情形,我需要统计昨天天玩家身上的金币存量。
玩家的金币存量在一天中是多次变动的,最后一次变动的值才是玩家身上的真实存量。
那么就需要找出最后一次的记录,简单的方法是先对uid做group by,max(time)找到每个玩家的最后一次时间。
再和表本身做自连接,找到每个玩家最后的一条记录,在从中提取金币存量字段。
比如下面的这段sql:
WITH
-- 找到每个玩家当天最大的时间戳
last_record AS (
SELECT
uid,
MAX(ts) AS ts
FROM
`金币变化记录表`
WHERE
DATE(ts) = "2020-12-01"
GROUP BY
1)
SELECT
gold_coin.uid,
remain_gold
FROM
`金币变化记录表` AS gold_coin
-- 通过自连接选出每个玩家最后一条记录
INNER JOIN
last_record
ON
gold_coin.uid = last_record.uid
AND gold_coin.ts = last_record.ts
WHERE
DATE(gold_coin.ts) = "2020-12-01"
如果在pandas中好几种可以获取最后最后一条记录的方法。
比如先按时间升序然后drop_duplicates(‘uid’, keep='last'),
又或者直接df.iloc[df.groupby('uid')['ts'].idxmax()]等,都比自连接简单的多。
在bigquery的语法中,提供了一种叫做分析函数的东西,
可以让我们不用自连接做到找出最后一条记录。
文档位置:标准 SQL 中的分析函数概念
简单来说,它可以在保留原行数的情况下,实现一些聚合分析的结果,并保存到相应的行上。
比如说,我们有下面的数据:
uid num
aa 10
aa 20
bb 3
bb 4
只要你使用了group by uid,那么结果中就只有两行数据,aa和bb。
但是使用分析函数后还是4行。
它会在一个滑动窗口内使用一些函数,然后把结果保存在本行上。
比如计算累计和,结果就是:
uid num cumsum
aa 10 10
aa 20 30
bb 3 3
bb 4 7
在这些分析函数中,有一个last_value函数,它可以取出本窗口内的最后一个值。
既然有了最后这个概念,就一定是要有顺序了,所以使用last_value函数也必须要指定order by 字段。
然后就是滑动窗口,默认是从第一个值到当前值,即
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
我们可以指定为从开头到结束
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
然后需要指定分组字段和排序字段,语法为:
PARTITION BY uid ORDER BY ts
这种语法是一种window的结构,它写在select语句的最后
然后再select last_value(remain_gold)就能拿到每个玩家的最后一个值了。
WITH
t AS (
SELECT
uid,
LAST_VALUE(server) OVER (item_window) AS server,
-- 从窗口中去最后一个值
LAST_VALUE(remain_gold) OVER (item_window) AS remain_num
FROM
`金币变化记录表`
WHERE
-- 在这个时间范围内增加或减少过gold,实际计算的值是每个玩家最后一次变化时的值
DATE(ts, '+08') BETWEEN '2020-11-1'
AND "2020-11-8"
-- 分析函数,开一个窗口
WINDOW
item_window AS (
-- 按照uid分组
PARTITION BY uid
-- 分组后再按照时间排序
ORDER BY ts
-- 滑动窗口的范围为从开始到结束
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ))
SELECT
uid,
server,
remain_num
FROM
t
GROUP BY 1, 2, 3
注意最后的group by 1 2 3语句,
在上面说过,使用分析函数后,行数是不变的,
这样就有重复的数据,一个玩家有多少次金币变化记录就有多少行的重复记录,
所有需要 group by做一次去重得到最后的结果。
虽然看起来sql行数差不多,但是分析函数提供了更多的可能性,比如上面的累加和,普通的sql就是无法完成的。
网友评论