SQL -获取每个PlayerID的最小日期

Question

我试图解决我的作业中的问题，他们要求我创建一个新表并添加一些新列(基于join表的计算)。

我被要求找出玩家在2021年1月1日之后进行购买的第一个日期，以及他进行另一次购买的最后日期(也是在2021年1月1日之后)。

我试着做以下事情:

select p.PlayerID, p.InstallDate, p.Platform, p.[Country ], p.LoginType, sum(ddn.Revenue + p.LifeTimeRevenue) as LTRevenueNew, min(ddn.[Date ]), max(ddn.[Date ])
--into dbo.PlayersNew
from dbo.Players p
inner join dbo.DailyDataNew ddn
on p.PlayerID = ddn.PlayerID
group by p.PlayerID, p.InstallDate, p.Platform, p.[Country ], p.LoginType, ddn.[Date ]
having convert(date, ddn.[Date ]) > '2020-11-26'

然而，我得到相同的日期为所有行，而不是得到每个playerID的相对(和正确)日期。

PlayerID    InstallDate Platform    Country     LoginType   LTRevenueNew    (No column name)    (No column name)
836 4/2/2015    Android US  Facebook    14382   2020-11-27 00:00:00.000 2020-11-27 00:00:00.000
836 4/2/2015    Android US  Facebook    14382   2020-11-28 00:00:00.000 2020-11-28 00:00:00.000
836 4/2/2015    Android US  Facebook    14382   2020-11-29 00:00:00.000 2020-11-29 00:00:00.000
836 4/2/2015    Android US  Facebook    14382   2020-11-30 00:00:00.000 2020-11-30 00:00:00.000
836 4/2/2015    Android US  Facebook    14382   2020-12-12 00:00:00.000 2020-12-12 00:00:00.000
836 4/2/2015    Android US  Facebook    14382   2020-12-13 00:00:00.000 2020-12-13 00:00:00.000

想法吗?

Answer 1

这可能没有帮助，这取决于你所在的学校为你设置了什么版本的SQL(我假设在虚拟机中，具有开发许可证)。如果这不起作用，你有其他的选择，比如使用相关子查询(新gross)来做同样的事情。同时我会帮你把它打出来。我还在这里放了一些注释，只是一般的东西。

请记住，这是未经测试的，但你所追求的是很容易得到的。希望能有所帮助!

--Proper indentation is your friend
--Doesnt matter hwo you do it as long as youre consistent
-- most people use sql formatters and leave it at that
-- tyr notepad++'s free "Poor man's sql formatter", its great
SELECT -- youll notice all my key words are capitalized
-- this isnt necessary but it helps readability a little bit
p.PlayerID
, p.InstallDate
, p.Platform
, p.[Country ]  -- spaces in objects names are a no-no
, p.LoginType
, SUM(ddn.Revenue + p.LifeTimeRevenue) as LTRevenueNew  
-- unless working with char restrictions 
-- due to quirky applications, 
-- try to abbreviate only when necessary
, MIN(ddn.[Date ]) OVER 
(
PARTITION BY p.PlayerID
ORDER BY ddn.[Date ]
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)       
, MAX(ddn.[Date ]) OVER 
(
PARTITION BY p.PlayerID
ORDER BY ddn.[Date ] DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
--INTO dbo.PlayersNew
FROM 
dbo.Players p
INNER JOIN dbo.DailyDataNew ddn
ON p.PlayerID = ddn.PlayerID
WHERE
ddn.[Date ])>= '2021-01-01'  -- date is a reserved word. 
-- do not name your objects 
-- the same as reserved words
GROUP BY 
p.PlayerID
, p.InstallDate
, p.Platform
, p.[Country ]
, p.LoginType

下面是相关子查询版本。注意，这并不是理想的，因为select会对每一行执行。我马上会给你提供第三个答案，可能没那么糟糕。

SELECT 
p.PlayerID
, p.InstallDate
, p.Platform
, p.[Country ]
, p.LoginType
, SUM(ddn.Revenue + p.LifeTimeRevenue) as LTRevenueNew  
,  
(
SELECT MIN(ddn.[Date ] 
FROM dbo.PlayersNew pn
WHERE pn.PlayerID = p.PlayerID
) AS MinDate    
-- this is a correlated subquery
-- a subquery is considered to be correlated
-- when it refers to an outer object
-- this is that reference "pn.PlayerID = p.PlayerID"
,  
(
SELECT MAX(ddn.[Date ] 
FROM dbo.PlayersNew pn
WHERE pn.PlayerID=p.PlayerID
) AS MaxDate    
--INTO dbo.PlayersNew
FROM 
dbo.Players p
INNER JOIN dbo.DailyDataNew ddn
ON p.PlayerID = ddn.PlayerID
WHERE
ddn.[Date ])>= '2021-01-01'
GROUP BY 
p.PlayerID
, p.InstallDate
, p.Platform
, p.[Country ]
, p.LoginType

还有一种方法可以解决这个问题。

SELECT 
p.PlayerID
, p.InstallDate
, p.Platform
, p.[Country ]
, p.LoginType
, SUM(ddn.Revenue + p.LifeTimeRevenue) as LTRevenueNew  
, mmd.MinDate   
, mmd.MaxDate   
--INTO dbo.PlayersNew
FROM 
dbo.Players p
INNER JOIN dbo.DailyDataNew ddn
ON p.PlayerID = ddn.PlayerID
INNER JOIN  -- JOIN on subquery for the data
(
SELECT 
dn.PlayerID
, MIN(ddn.[Date ]) AS MinDate
, MAX(ddn.[Date ]) AS MaxDate
FROM 
dbo.DailyDataNew dn 
WHERE
ddn.[Date ])>= '2021-01-01'
GROUP BY dn.PlayerID
) mmd ON p.PlayerID =mmd.PlayerID 
AND ddn.PlayerID = mmd.PlayerID
WHERE
ddn.[Date ])>= '2021-01-01'
GROUP BY 
p.PlayerID
, p.InstallDate
, p.Platform
, p.[Country ]
, p.LoginType

Answer 2

阅读您的描述和到目前为止您所拥有的内容，似乎您可以利用apply()-您可以将DailyDataNew表关联到PlayerId上，并在apply()中进行聚合和过滤。

select p.PlayerID, p.InstallDate, p.Platform, p.Country, p.LoginType, 
ddn.Revenue + p.LifeTimeRevenue as LTRevenueNew, ddn.MinDate, ddn.MaxDate
from dbo.Players p
cross apply (
select min(ddn.[Date]) MinDate, max(ddn.[Date]) MaxDate, sum(ddn.Revenue) Revenue
from dbo.DailyDataNew ddn
where ddn.PlayerID = p.PlayerID and ddn.[Date] > '20210101'
)ddn

我还假设空格后缀(希望)是打字错误。请注意，当指定文字日期值时，最好使用ISO格式YYYYMMDD date，这样可以消除歧义，并避免由于区域设置而导致误读。