sql - Nested COUNT with WHERE clause in SELECT statement -
i'm novice in sql scripts , trying counts table count 3 different ways, count(1) not issue, next 2 counts slow.
there individual indexes on columns used in clause still slow. there easier , better way these count?
in following code want unique customerid's on each parent-select group timestamp:
(select count(distinct al2.customerid) actionlog al2 convert(varchar(10),al2.timestamp,110) = convert(varchar(10),al1.timestamp,110) , al2.actiontypeid = al1.actiontypeid , al2.campaignid = al1.campaignid , al2.eventid = al1.eventid , case when charindex('?', al2.url) > 0 left(url, charindex('?', al2.url)-1) else url end = case when charindex('?', al1.url) > 0 left(url, charindex('?', al1.url)-1) else url end , al2.timestamp between dateadd(dd, datediff(dd, 0, getdate()),-7) , dateadd(dd, datediff(dd, 0, getdate()),0)) count_total_dayunique the next 1 want new unique customerid's have not been part of parent-select group clause before:
(select count(distinct al3.customerid) actionlog al3 al3.actiontypeid = al1.actiontypeid , al3.campaignid = al1.campaignid , al3.eventid = al1.eventid , case when charindex('?', al3.url) > 0 left(url, charindex('?', al3.url)-1) else url end = case when charindex('?', al1.url) > 0 left(url, charindex('?', al1.url)-1) else url end , al3.customerid not in (select count(distinct al4.customerid) actionlog al4 al4.actiontypeid = al1.actiontypeid , al4.campaignid = al1.campaignid , al4.eventid = al1.eventid , case when charindex('?', al4.url) > 0 left(url, charindex('?', al3.url)-1) else url end = case when charindex('?', al1.url) > 0 left(url, charindex('?', al1.url)-1) else url end , al4.timestamp > dateadd(dd, -1, convert(varchar(10),al1.timestamp,110)))) count_total_uniqueonevent please see entire script below:
select convert(varchar(10),timestamp,110) timestamp ,activitytypeid ,actiontypeid ,campaignid ,eventid ,count(1) count_total_day ,(select count(distinct al2.customerid) actionlog al2 convert(varchar(10),al2.timestamp,110) = convert(varchar(10),al1.timestamp,110) , al2.actiontypeid = al1.actiontypeid , al2.campaignid = al1.campaignid , al2.eventid = al1.eventid , case when charindex('?', al2.url) > 0 left(url, charindex('?', al2.url)-1) else url end = case when charindex('?', al1.url) > 0 left(url, charindex('?', al1.url)-1) else url end , al2.timestamp between dateadd(dd, datediff(dd, 0, getdate()),-7) , dateadd(dd, datediff(dd, 0, getdate()),0)) count_total_dayunique ,(select count(distinct al3.customerid) actionlog al3 al3.actiontypeid = al1.actiontypeid , al3.campaignid = al1.campaignid , al3.eventid = al1.eventid , case when charindex('?', al3.url) > 0 left(url, charindex('?', al3.url)-1) else url end = case when charindex('?', al1.url) > 0 left(url, charindex('?', al1.url)-1) else url end , al3.customerid not in (select count(distinct al4.customerid) actionlog al4 al4.actiontypeid = al1.actiontypeid , al4.campaignid = al1.campaignid , al4.eventid = al1.eventid , case when charindex('?', al4.url) > 0 left(url, charindex('?', al3.url)-1) else url end = case when charindex('?', al1.url) > 0 left(url, charindex('?', al1.url)-1) else url end , al4.timestamp > dateadd(dd, -1, convert(varchar(10),al1.timestamp,110)))) count_total_uniqueonevent ,reporttag ,case when charindex('?', url) > 0 left(url, charindex('?', url)-1) else url end url ,templateid actionlog al1 timestamp between dateadd(dd, datediff(dd, 0, getdate()),-7) , dateadd(dd, datediff(dd, 0, getdate()),0) group convert(varchar(10),timestamp,110),activitytypeid,actiontypeid,campaignid,eventid,reporttag,url,templateid i'll try give background on database, sample data , expected result.
database structure:
[dbo].[actionlog]( [id] [int] identity(1,1) not null, [timestamp] [datetime] null constraint [df_actionlog] default (getdate()), [customerid] [int] null, [activitytypeid] [int] null, [actiontypeid] [int] null, [campaignid] [int] null, [eventid] [int] null, [reporttag] [varchar](500) null, [url] [varchar](500) null, [templateid] [int] null, ) sample:
id timestamp customerid activitytypeid actiontypeid campaignid eventid reporttag url templateid 1 2014-09-24 11:55:27.900 1 1 12 35 68 null null null 2 2014-09-24 12:58:26.710 2 1 12 35 68 null null null 3 2014-09-24 13:54:34.993 2 1 12 35 68 null null null 4 2014-09-24 16:35:33.810 4 1 12 35 68 null null null 5 2014-09-24 16:53:17.623 1 1 12 35 68 null null null 6 2014-09-25 20:36:30.190 1 1 12 35 68 null null null 7 2014-09-25 20:36:33.050 4 1 12 35 68 null null null 8 2014-09-25 23:35:38.520 3 1 12 35 68 null null null 9 2014-09-25 08:35:15.247 4 1 12 35 68 null null null 10 2014-09-25 08:36:11.363 6 1 12 35 68 null null null 11 2014-09-26 11:23:58.223 1 1 12 35 68 null null null 12 2014-09-26 11:55:12.640 2 1 12 35 68 null null null 13 2014-09-26 12:03:28.563 6 1 12 35 68 null null null 14 2014-09-26 12:39:53.003 7 1 12 35 68 null null null 15 2014-09-26 15:55:55.843 8 1 12 35 68 null null null 16 2014-09-27 15:55:55.890 1 1 12 35 68 null null null 17 2014-09-27 16:22:05.540 3 1 12 35 68 null null null 18 2014-09-27 17:34:43.093 8 1 12 35 68 null null null 19 2014-09-27 09:40:23.743 9 1 12 35 68 null null null 20 2014-09-27 10:08:50.240 11 1 12 35 68 null null null 21 2014-09-28 10:12:37.330 11 1 12 35 68 null null null 22 2014-09-28 11:22:26.413 12 1 12 35 68 null null null 23 2014-09-28 11:23:06.520 13 1 12 35 68 null null null 24 2014-09-28 11:52:26.757 14 1 12 35 68 null null null 25 2014-09-28 13:05:13.850 15 1 12 35 68 null null null 26 2014-09-29 13:05:24.900 16 1 12 35 68 null null null 27 2014-09-29 13:06:07.017 16 1 12 35 68 null null null 28 2014-09-29 13:07:26.993 14 1 12 35 68 null null null 29 2014-09-29 14:13:04.893 13 1 12 35 68 null null null 30 2014-09-29 19:54:11.350 12 1 12 35 68 null null null my expected result:
timestamp activitytypeid actiontypeid campaignid eventid count count_total_dayunique count_total_uniqueonevent report url templateid 2014-09-24 1 12 35 68 5 3 3 null null null 2014-09-25 1 12 35 68 5 4 2 null null null 2014-09-26 1 12 35 68 5 5 2 null null null 2014-09-27 1 12 35 68 5 5 3 null null null 2014-09-28 1 12 35 68 5 5 4 null null null 2014-09-29 1 12 35 68 5 4 1 null null null i hope there can me out here.
thanks in advance
for subquery:
(select count(distinct al2.customerid) actionlog al2 convert(varchar(10),al2.timestamp,110) = convert(varchar(10),al1.timestamp,110) , al2.actiontypeid = al1.actiontypeid , al2.campaignid = al1.campaignid , al2.eventid = al1.eventid , (case when charindex('?', al2.url) > 0 left(url, charindex('?', al2.url)-1) else url end) = (case when charindex('?', al1.url) > 0 left(url, charindex('?', al1.url)-1) else url end) , al2.timestamp between dateadd(day, datediff(dd, 0, getdate()),-7) , dateadd(day, datediff(dd, 0, getdate()),0) ) count_total_dayunique this quite complicated. can start index on actionlog(actiontypeid, campaignid, eventid, timestamp, url, customerid). should cover subquery, index used subquery.
Comments
Post a Comment