基于PostgreSQL数据库的周、月数据统计方法

2019独角兽企业重金招聘Python工程师标准>>>

最近碰到一个需求是统计每周的周每个用户的局数总和、每月每个用户的局数总和，首先有日表记录每个用户每天的局数之和，日表的表结构如下：

create table user_day(
    log_date date not null,
    user_id int not null,
    round_count int not null
);

表中数据如下：

那么我们可以利用extract这个函数把日数据聚合为周数据，SQL如下：

SELECT
	log_year,
	log_week,
	user_id,
	SUM (round_count) AS round_count
FROM
	(
		SELECT
			EXTRACT (YEAR FROM log_date) AS log_year,
			EXTRACT (week FROM log_date) AS log_week,
			user_id,
			round_count
		FROM
			user_day
	) AS A
GROUP BY
	log_year,
	log_week,
	user_id
ORDER BY
	log_year,
	log_week,
	user_id;

extract函数可以从date类型或timestamp类型的变量中提取出年、月、日、时、分、秒这些数据，因此可以从上述的log_date中提取出年和周来，将日数据变为周数据，再对相同周进行group by即可得到聚合后的周数据，如下所示：

同理，可以从上述的log_date中提取出年和月来，然后将日数据变为月数据，再对相同的月进行group by即可得到聚合后的月数据，SQL如下：

SELECT
	log_year,
	log_month,
	user_id,
	SUM (round_count) AS round_count
FROM
	(
		SELECT
			EXTRACT (YEAR FROM log_date) AS log_year,
			EXTRACT (MONTH FROM log_date) AS log_month,
			user_id,
			round_count
		FROM
			user_day
	) AS A
GROUP BY
	log_year,
	log_month,
	user_id
ORDER BY
	log_year,
	log_month,
	user_id;

查询结果如下：

转载于:https://my.oschina.net/u/3400816/blog/1914430