高分求优化一条sql语句

Death_Spank 2012-05-03 12:43:51 


select * from (

	select * from (

		select * from (

			select distinct 

				replace(convert(varchar, gamelog.intime, 102),'.','-') as date1,

				count(*) as vistCount

			from 

				gamelog  

			where 

				intime between '2011-06-01 00:00:00' and '2011-06-15 00:00:00' and factoryId=11

			group by  

				replace(convert(varchar, gamelog.intime, 102),'.','-')

		) as ta right join (

			select distinct 

				replace(convert(varchar, gamelog.intime, 102),'.','-') as date2,

				count(distinct iptvName) as userCount

			from 

				gamelog  

			where 

				intime between '2011-06-01 00:00:00' and '2011-06-15 00:00:00' and factoryId=11

			group by  

				replace(convert(varchar, gamelog.intime, 102),'.','-')

		) as tb on date1=date2

	)as ab left join (

			select distinct 

				replace(convert(varchar, gamelog.intime, 102),'.','-') as date3,

				cast(sum(datediff(s,outtime,intime)) /3600.00 as decimal(18,2)) as playTime 

			from 

				gamelog  

			where 

				intime between '2011-06-01 00:00:00' and '2011-06-15 00:00:00' and factoryId=11

			group by  

				replace(convert(varchar, gamelog.intime, 102),'.','-')

		) as tc



	 on date2=date3

) as abc left join (

		select replace(convert(varchar, Ta.intime, 102),'.','-')as date4,COUNT(Ta.iptvName) as newUserCount 

		from (select *,rownum= ROW_NUMBER ()over(partition by iptvName order by intime) 

			  from gamelog 

			  where intime between '2011-06-01 00:00:00' and '2011-06-15 00:00:00' and factoryId=11 and 

					iptvName not in (select distinct iptvName from gamelog where intime<'2011-06-01 00:00:00') )as Ta 

		where Ta.rownum =1 group by  replace(convert(varchar, Ta.intime, 102),'.','-')

)as td on date2=date4


数据：
logId,factoryId,gameId,intime,outtime


971811,8,23,2011-06-02 15:36:11.210,2011-06-02 15:36:10.420
971812,8,22,2011-06-02 15:39:03.487,2011-06-02 15:39:02.530
971813,8,22,2011-06-02 15:41:17.630,2011-07-02 15:41:17.630
971814,8,-1,2011-06-02 15:41:23.660,2011-07-02 15:41:23.660
971815,8,22,2011-06-02 15:42:04.027,2011-07-02 15:42:04.027
971816,8,21,2011-06-02 15:45:02.083,2011-06-02 15:45:01.203
971817,8,19,2011-06-02 15:49:55.947,2011-07-02 15:49:55.947
971818,8,19,2011-06-02 15:50:21.693,2011-07-02 15:50:21.693
971819,8,19,2011-06-02 15:53:56.373,2011-07-02 15:53:56.373
971820,8,19,2011-06-02 15:54:00.087,2011-07-02 15:54:00.087
971821,8,22,2011-06-02 15:54:04.997,2011-07-02 15:54:04.997
971822,8,11,2011-06-02 15:54:08.520,2011-06-02 15:54:07.580
971823,8,19,2011-06-02 16:01:55.307,2011-06-02 16:01:55.640
971824,8,19,2011-06-02 16:14:57.620,2011-06-02 16:14:57.970
971825,8,19,2011-06-02 16:17:27.730,2011-06-02 16:17:28.060
971826,10,15,2011-06-02 16:19:57.510,2011-07-02 16:19:57.510
971827,8,20,2011-06-02 16:34:10.107,2011-06-02 16:34:10.420
971828,8,19,2011-06-02 16:40:15.387,2011-06-02 16:40:15.720
971829,8,19,2011-06-02 16:47:02.497,2011-06-02 16:47:02.813
971830,8,-1,2011-06-02 17:19:08.980,2011-07-02 17:19:08.980
971831,10,-1,2011-06-02 17:19:39.793,2011-07-02 17:19:39.793



这张表里一共有400W条数据，在用这个sql语句速度很慢，小弟对sql知识了解颇少，求指教
...全文
203 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
十林 2012-05-07
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 的回复:]

引用 6 楼 的回复:

楼主试验数据不完整，所以下面如果有误请指正。

楼主要group by date 可以使用 CONVERT(VARCHAR(10), GG.INTIME, 102) AS DATE 只获得intime 的前十个字符，即日期。

还有query 中的前三个subquery 可以合并在一起，因为都是按照date 来group by的。

最后一个query……
[/Quote]
希望楼主能解释一下 最后一个query中 业务逻辑上的意义，还有楼主其中用到了row_number 是为了什么？其中还有一个 not in条件是为了取哪些数据？ 这个讲清楚了，优化query就简单些了。
damo_baby 2012-05-06
  • 打赏
  • 举报
 回复
优化原则：
1、集合处理
2、使用SGAG模式

如
select distinct
replace(convert(varchar, gamelog.intime, 102),'.','-') as date1,
count(*) as vistCount
from
gamelog
where
intime between '2011-06-01 00:00:00' and '2011-06-15 00:00:00' and factoryId=11
group by
replace(convert(varchar, gamelog.intime, 102),'.','-')
这个多处使用，建议使用临时表

2、 not in 不要使用，建议使用 not exists
3、 建议组合索引 create index t_index on gamelog(factoryId,intime)
条件改成 factoryId=11 and intime between '2011-06-01 00:00:00' and '2011-06-15 00:00:00'

希望你能理解。
siegebaoniu 2012-05-05
  • 打赏
  • 举报
 回复
建议把原始表和想要的结果写出来，要的逻辑描述一下，光看LZ的SQL，太累了。。。
Death_Spank 2012-05-03
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 的回复:]

replace(convert(varchar, gamelog.intime, 102),'.','-')
改为：
convert(varchar, gamelog.intime, 120)
[/Quote]
关键是我想group by 在同一天的
--小F-- 2012-05-03
  • 打赏
  • 举报
 回复
几个子查询的内容大部分都一样 只有少数不一样的

完全可以考虑临时表过滤相同部分 不同的部分再写出来。
xuam 2012-05-03
  • 打赏
  • 举报
 回复
replace(convert(varchar, gamelog.intime, 102),'.','-')
改为：
convert(varchar, gamelog.intime, 120)
Death_Spank 2012-05-03
  • 打赏
  • 举报
 回复
补充一下
'2011-06-01 00:00:00' 这个对应存储过程的参数是 @startTime
'2011-06-15 00:00:00' @endTime
factoryId=11 @factoryId
tjs_125 2012-05-03
  • 打赏
  • 举报
 回复
我觉得你的查询分组条件与抑制了索引的使用，应该考虑怎么避免！
开着拖拉机泡妞 2012-05-03
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 的回复:]

引用 6 楼 的回复:

楼主试验数据不完整，所以下面如果有误请指正。

楼主要group by date 可以使用 CONVERT(VARCHAR(10), GG.INTIME, 102) AS DATE 只获得intime 的前十个字符，即日期。

还有query 中的前三个subquery 可以合并在一起，因为都是按照date 来group by的。

最后一个query……
[/Quote]


你可以考虑三楼的方法。或者你觉得不行，那你就给测试数据和你对应的算法以及对应的结果，看看大家有没有好的方法
Death_Spank 2012-05-03
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 的回复:]

楼主试验数据不完整，所以下面如果有误请指正。

楼主要group by date 可以使用 CONVERT(VARCHAR(10), GG.INTIME, 102) AS DATE 只获得intime 的前十个字符，即日期。

还有query 中的前三个subquery 可以合并在一起，因为都是按照date 来group by的。

最后一个query的功能不清楚，不然应该也可以优化……
[/Quote]

上面的3个query分开来会比较快，您说的下面新增用户有什么优化方法吗，我查了下 这个新增用户的query确实比较浪费时间
tjs_125 2012-05-03
  • 打赏
  • 举报
 回复
赞成3楼的做法，你可以按下面的思路去试试
if Object_id('Tmp_gamelog') si not null Drop table Tmp_gamelog;

select  *, replace(convert(varchar, intime, 102),'.','-') as date

into Tmp_gamelog

from gamelog  

where intime between '2011-06-01 00:00:00' and '2011-06-15 00:00:00' and factoryId=11;



create Index Tmp_gamelog_data

on Tmp_gamelog(date);



--其他你需要建立的索引

--.....



--然后对 Tmp_gamelog 查询，利用索引，应该会快很多

另外你说的group by 在同一天，我看你的语句
replace(convert(varchar, gamelog.intime, 102),'.','-')
，
这个不表示同一天的。如果要同一天可以用 
convert(varchar(10), gamelog.intime, 120)

十林 2012-05-03
  • 打赏
  • 举报
 回复
楼主试验数据不完整，所以下面如果有误请指正。

楼主要group by date 可以使用 CONVERT(VARCHAR(10), GG.INTIME, 102) AS DATE 只获得intime 的前十个字符，即日期。

还有query 中的前三个subquery 可以合并在一起，因为都是按照date 来group by的。

最后一个query的功能不清楚，不然应该也可以优化一下的。

SELECT 

MQ1.*,

MQ2.NEWUSERCOUNT

FROM (SELECT  CONVERT(VARCHAR(10), GG.INTIME, 102) AS DATE,

        COUNT(GG.*) AS VISTCOUNT,

		COUNT(DISTINCT GG.IPTVNAME) AS USERCOUNT,

		CAST(SUM(DATEDIFF(s,GG.OUTTIME,GG.INTIME)) /3600.00 AS DECIMAL(18,2)) AS PLAYTIME

FROM 

GAMELOG GG



WHERE GG.INTIME BETWEEN '2011-06-01 00:00:00' AND '2011-06-15 00:00:00' AND GG.FACTORYID=11

GROUP BY  CONVERT(VARCHAR(10), GG.INTIME, 102)) MQ1





--以下这个query 不知道楼主为了啥？？能说明一下吗？ 如果单纯为了取时间2011-06-01 00:00:00之后的新用户，貌似不用这么复杂

LEFT JOIN (

        SELECT CONVERT(VARCHAR(10), TA.INTIME, 102) AS DATE4,COUNT(TA.IPTVNAME) AS NEWUSERCOUNT 

        FROM (SELECT *,ROWNUM= ROW_NUMBER ()OVER(PARTITION BY IPTVNAME ORDER BY INTIME) 

              FROM GAMELOG 

              WHERE INTIME BETWEEN '2011-06-01 00:00:00' AND '2011-06-15 00:00:00' AND FACTORYID=11 AND 

                    IPTVNAME NOT IN (SELECT DISTINCT IPTVNAME FROM GAMELOG WHERE INTIME<'2011-06-01 00:00:00') )AS TA 

        WHERE TA.ROWNUM =1 GROUP BY  CONVERT(VARCHAR(10), TA.INTIME, 102)MQ2

ON MQ1.DATE=MQ2.DATE4
simonxt 2012-05-03
  • 打赏
  • 举报
 回复
IPTV ?

1、你的时间如果只是做比较用的话就不要在嵌套查询里面做类似这种处理，
replace(convert(varchar, gamelog.intime, 102),'.','-') as date3,
比较同一天可以用 where datediff(day, datetime1, datetime2)=0 做条件，
在得出最终结果后将要显示的日期写成2楼说的模式

2、嵌套较多，或子查询比较复杂的情况下，建议用用临时表或表变量
一条sql语句查出男生前5名和女生前五名
要求用一条sql语句查出男生前5名和女生前五名    select * from t_stu a where 5>(select count(*) FROM t_stu where gender=a.gender AND grade>a.grade) order by a.grade desc   对这个sql的理解是 对于每
【②MySQL 】：测试数据准备、SQL语句规范与基本操作
MySQL 数据库实用指南：测试数据准备、SQL语句规范与基本操作
SQL 语句在查询分析器执行很快，程序 Dapper 参数化查询就很慢（parameter-sniffing）（转）...
这个问题困扰我好长时间了，使用SQLSERVER 事务探查器找到执行超时的SQL语句，参数查询都是通过执行exe sp_executesql 的存储过程调用，因为它能够分析并缓存查询计划，从而优化查询效率，但是现在反而很慢。...
【SQL】使用SQL查询各个课程的最高分、第二高分、最高分人数
SQL 查询各个课程最高分、第二高分
sql 语句的limit的用法
sql 语句的limit的用法 SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15 //为了检索从某一个偏移量到记录...
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章