sqlserver-累和

1.练习题

2.数据集

USE master
GO

IF EXISTS(SELECT * FROM sysdatabases WHERE name='LeetCode')
	DROP DATABASE LeetCode
GO
CREATE DATABASE LeetCode
GO
USE LeetCode
GO

IF EXISTS(SELECT * FROM sysobjects WHERE name='Activity')
	DROP TABLE Activity
GO
CREATE TABLE Activity
(
	player_id INT,
	device_id INT,
	event_date DATE,
	games_played INT,
	CONSTRAINT PK_player PRIMARY KEY(player_id, event_date)
)
INSERT INTO Activity
VALUES
	(1, 2, '2016-03-01', 5), (1, 2, '2016-05-02', 6), (1, 3, '2017-06-25', 1),
	(3, 1, '2016-03-02', 0), (3, 4, '2018-07-03', 5)
GO

SELECT *
FROM Activity
GO

3.解

-- 只保留当日第一次登陆时间
SELECT player_id, 
	event_date, 
	--COUNT(event_date) OVER(PARTITION BY player_id ORDER BY event_date),
	SUM(games_played) OVER(PARTITION BY player_id ORDER BY event_date)
FROM Activity
ORDER BY player_id ASC