CREATE TABLE [UserEdemaHeartProfitRanking](
[rId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_UserEdemaHeartProfitRanking_rId] DEFAULT (newid()),
[rUserId] [uniqueidentifier] NOT NULL,
[rMoney] [money] NOT NULL,
[rDate] [datetime] NOT NULL)此表数据大概有400万
要求统计出60天之内,赚钱比例最多,且数据条数大于60的Top 30用户ID, 其中rMoney>0 表示赚钱,rMoney<0表示出钱,为了精确的排序,我的Sql语句中把比例精确到了4位数。
rUserId,rDate都做了非聚集索引,rId是主键
SELECT TOP 30
rUserId
FROM ( SELECT SUM(1) AS raceCount ,
CAST(CAST(SUM(CASE WHEN rMoney > 0 THEN 1
ELSE 0
END) AS DECIMAL) / CAST(SUM(1) AS DECIMAL) AS DECIMAL(10,
4)) AS userSeniority ,
rUserId
FROM dbo.UserEdemaHeartProfitRanking
WHERE DateDiff(d, rDate,getdate())<60
GROUP BY rUserId
) AS tempRank
WHERE raceCount > 60
ORDER BY userSeniority DESC 这是我目前的语句,运行时间大概40s,因为有6、7个类似的统计。例如还要统计30天之内,条数大于30的,90天之内条数大于90的,等等。一次性跑完耗时就有5、6分钟了。放网站里面就超时了。另外表中的数据是可删减的,不一定只有增,求高手们的优化建议。
[rId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_UserEdemaHeartProfitRanking_rId] DEFAULT (newid()),
[rUserId] [uniqueidentifier] NOT NULL,
[rMoney] [money] NOT NULL,
[rDate] [datetime] NOT NULL)此表数据大概有400万
要求统计出60天之内,赚钱比例最多,且数据条数大于60的Top 30用户ID, 其中rMoney>0 表示赚钱,rMoney<0表示出钱,为了精确的排序,我的Sql语句中把比例精确到了4位数。
rUserId,rDate都做了非聚集索引,rId是主键
SELECT TOP 30
rUserId
FROM ( SELECT SUM(1) AS raceCount ,
CAST(CAST(SUM(CASE WHEN rMoney > 0 THEN 1
ELSE 0
END) AS DECIMAL) / CAST(SUM(1) AS DECIMAL) AS DECIMAL(10,
4)) AS userSeniority ,
rUserId
FROM dbo.UserEdemaHeartProfitRanking
WHERE DateDiff(d, rDate,getdate())<60
GROUP BY rUserId
) AS tempRank
WHERE raceCount > 60
ORDER BY userSeniority DESC 这是我目前的语句,运行时间大概40s,因为有6、7个类似的统计。例如还要统计30天之内,条数大于30的,90天之内条数大于90的,等等。一次性跑完耗时就有5、6分钟了。放网站里面就超时了。另外表中的数据是可删减的,不一定只有增,求高手们的优化建议。
FROM dbo.UserEdemaHeartProfitRanking
WHERE DateDiff(d, rDate,getdate())<60
GROUP BY rUserId having SUM(1)>60
ORDER BY CAST(CAST(SUM(CASE WHEN rMoney > 0 THEN 1
ELSE 0 END) AS DECIMAL) / CAST(SUM(1)) AS DECIMAL(10,
4))DESC
FROM dbo.UserEdemaHeartProfitRanking
WHERE DateDiff(d, rDate,getdate())<60
GROUP BY rUserId having count(*)>60
ORDER BY CAST(SUM(CASE WHEN rMoney > 0 THEN 1
ELSE 0 END)*1.0/count(*)) AS DECIMAL(10,4))DESC
---这样试试?另外,你为什么还要精确到4位小数
SELECT top 30 rUserId
FROM dbo.UserEdemaHeartProfitRanking
WHERE rDate between dateadd(day,-60,getdate()) and getdate()
GROUP BY rUserId having count(*)>60
ORDER BY SUM(CASE WHEN rMoney > 0 THEN 1
ELSE 0 END)*1.0/count(*)DESC
ELSE 0 END)*1.0/count(*)很精确了。
SELECT top 30 rUserId into #tb
FROM dbo.UserEdemaHeartProfitRanking
WHERE rDate between dateadd(day,-60,getdate()) and getdate()
GROUP BY rUserId having count(*)>60
ORDER BY SUM(CASE WHEN rMoney > 0 THEN 1
ELSE 0 END)*1.0/count(*)DESC update tb 连接 #tb睡了,喝多了,有点晕