Untitled
unknown
sql
2 years ago
6.8 kB
1
Indexable
Never
SELECT COUNT(1) FROM MKTGMART.HITS_ARCHIVE_JULY2021(NOLOCK) BS WHERE BS.HITS_TYPE = 'EVENT' AND DATE BETWEEN '20210301' AND '20210331'-- 21,45,85,893 --NTP BASE: IF OBJECT_ID('TEMP.NTP_FVIDS') IS NOT NULL DROP TABLE TEMP.NTP_FVIDS CREATE TABLE TEMP.NTP_FVIDS WITH (DISTRIBUTION = HASH(FULLVISITORID), HEAP) AS SELECT HTS.FULLVISITORID, MIN(HTS.DATE) AS MIN_DATE FROM (SELECT FULLVISITORID, DATE FROM MKTGMART.HITS_ARCHIVE_JULY2021(NOLOCK) WHERE HITS_TYPE = 'EVENT' AND HITS_ISINTERACTION = 'TRUE' AND DATE <= '20210331' UNION SELECT FULLVISITORID, DATE FROM MKTGMART.HITS_ARCHIVE(NOLOCK) WHERE HITS_TYPE = 'EVENT' AND HITS_ISINTERACTION = 'TRUE' AND DATE <= '20210331') HTS GROUP BY HTS.FULLVISITORID HAVING MIN(HTS.DATE) BETWEEN '20210101' AND '20210331' CREATE STATISTICS STAT_FULLVISITORID ON TEMP.NTP_FVIDS(FULLVISITORID)-- FVIDS(NTP Q1): 2,75,80,247 --LATEST VISITS: IF OBJECT_ID('TEMP.HIT_LATEST_VISIT') IS NOT NULL DROP TABLE TEMP.HIT_LATEST_VISIT CREATE TABLE TEMP.HIT_LATEST_VISIT WITH (DISTRIBUTION = HASH(FULLVISITORID), HEAP) AS SELECT HTS.FULLVISITORID, CONVERT(VARCHAR(8),DATEADD(MONTH, -3, MAX(HTS.DATE)),112) AS START_DATE, MAX(HTS.DATE) AS END_DATE FROM MKTGMART.HITS_ARCHIVE_JULY2021(NOLOCK) HTS INNER JOIN TEMP.NTP_FVIDS(NOLOCK) BS ON HTS.FULLVISITORID = BS.FULLVISITORID WHERE HTS.HITS_TYPE = 'EVENT' AND HTS.DATE BETWEEN '20210301' AND '20210331' AND HTS.HITS_ISINTERACTION = 'TRUE' AND HTS.HITS_PAGE_PAGEPATH NOT LIKE '/TVC_VP/%' AND HTS.HITS_PAGE_PAGEPATH NOT LIKE '/DG[_]%' AND HITS_PAGE_PAGEPATH NOT LIKE '/LEADMODEL%' AND HTS.HITS_PAGE_HOSTNAME NOT LIKE '%DEV%' AND HITS_PAGE_PAGEPATH NOT LIKE '/PPLONLINE%' AND HITS_PAGE_PAGEPATH NOT LIKE '/CUSTOMER/FORGETPASSWORD%' AND HTS.HITS_PAGE_PAGEPATH NOT LIKE '%THANK%' AND HTS.HITS_PAGE_HOSTNAME NOT LIKE '[0-9]%' AND HTS.HITS_PAGE_HOSTNAME NOT LIKE 'UAT%' AND HTS.HITS_PAGE_HOSTNAME NOT LIKE '%ADMIN%' AND HTS.HITS_PAGE_HOSTNAME NOT IN ('CUSTOMER360.BAJAJFINSERV.IN','','CONT-SITES-OLD.BAJAJFINSERV.IN', 'CONT-SITES.BAJAJFINSERV.IN', 'CONT-SITES.BAJAJFINESERV.IN', 'CONT-SITES-NEW.BAJAJFINSERV.IN', 'CONT-SITES1.BAJAJFINSERV.IN','LOCALHOST') GROUP BY HTS.FULLVISITORID CREATE INDEX FULLVISITORID ON TEMP.HIT_LATEST_VISIT(FULLVISITORID) CREATE INDEX START_DATE ON TEMP.HIT_LATEST_VISIT(START_DATE) CREATE INDEX END_DATE ON TEMP.HIT_LATEST_VISIT(END_DATE) CREATE STATISTICS STAT_FULLVISITORID ON TEMP.HIT_LATEST_VISIT(FULLVISITORID)-- NTP FVIDS ACTIVE IN MARCH-2021: 1,29,87,840 --SELECT COUNT(1) AS CNT, COUNT(DISTINCT FULLVISITORID) AS DST_FVID FROM TEMP.HIT_LATEST_VISIT(NOLOCK) -- CLEARNING HITS DATA FOR EXTRA #'S IF OBJECT_ID('TEMP.HITS_CLEAN_BASE_1') IS NOT NULL DROP TABLE TEMP.HITS_CLEAN_BASE_1 CREATE TABLE TEMP.HITS_CLEAN_BASE_1 WITH (DISTRIBUTION = HASH(FULLVISITORID), HEAP) AS SELECT HTS.FULLVISITORID, HTS.DATE AS HIT_DATE, HTS.VISITID, HTS.HITS_HITNUMBER, HTS.HITS_PAGE_HOSTNAME, VISITSTARTTIME, CASE WHEN CHARINDEX('#',HTS.HITS_PAGE_PAGEPATH)>1 THEN LEFT(HTS.HITS_PAGE_PAGEPATH, CHARINDEX('#',HTS.HITS_PAGE_PAGEPATH)-1) ELSE HTS.HITS_PAGE_PAGEPATH END AS HITS_PAGE_PAGEPATH, HTS.HITS_EVENTINFO_EVENTACTION, HTS.HITS_EVENTINFO_EVENTLABEL FROM MKTGMART.HITS_ARCHIVE_JULY2021(NOLOCK) HTS INNER JOIN TEMP.HIT_LATEST_VISIT(NOLOCK) AB ON HTS.FULLVISITORID = AB.FULLVISITORID AND HTS.DATE BETWEEN AB.START_DATE AND AB.END_DATE WHERE HTS.HITS_TYPE = 'EVENT' AND HTS.HITS_ISINTERACTION = 'TRUE' AND HTS.HITS_PAGE_PAGEPATH NOT LIKE '/TVC_VP/%' AND HTS.HITS_PAGE_PAGEPATH NOT LIKE '/DG[_]%' AND HITS_PAGE_PAGEPATH NOT LIKE '/LEADMODEL%' AND HTS.HITS_PAGE_HOSTNAME NOT LIKE '%DEV%' AND HITS_PAGE_PAGEPATH NOT LIKE '/PPLONLINE%' AND HITS_PAGE_PAGEPATH NOT LIKE '/CUSTOMER/FORGETPASSWORD%' AND HTS.HITS_PAGE_PAGEPATH NOT LIKE '%THANK%' AND HTS.HITS_PAGE_HOSTNAME NOT LIKE '[0-9]%' AND HTS.HITS_PAGE_HOSTNAME NOT LIKE 'UAT%' AND HTS.HITS_PAGE_HOSTNAME NOT LIKE '%ADMIN%' AND HITS_EVENTINFO_EVENTACTION NOT IN ('USER LOCATION','USER LOCATION ERROR') AND HTS.HITS_PAGE_HOSTNAME NOT IN ('CUSTOMER360.BAJAJFINSERV.IN','','CONT-SITES-OLD.BAJAJFINSERV.IN', 'CONT-SITES.BAJAJFINSERV.IN', 'CONT-SITES.BAJAJFINESERV.IN', 'CONT-SITES-NEW.BAJAJFINSERV.IN', 'CONT-SITES1.BAJAJFINSERV.IN','LOCALHOST') CREATE STATISTICS STAT_FULLVISITORID ON TEMP.HITS_CLEAN_BASE_1(FULLVISITORID)--(9,77,93,547 AFFECTED); FVIDS: 91,59,136 --SELECT COUNT(1) AS CNT, COUNT(DISTINCT FULLVISITORID) AS DST_FVID FROM TEMP.HITS_CLEAN_BASE_1(NOLOCK) -- CLEARNING HITS DATA FOR EXTRA &'S IF OBJECT_ID('TEMP.HITS_CLEAN_BASE_2') IS NOT NULL DROP TABLE TEMP.HITS_CLEAN_BASE_2 CREATE TABLE TEMP.HITS_CLEAN_BASE_2 WITH (DISTRIBUTION = HASH(FULLVISITORID), HEAP) AS SELECT BS.FULLVISITORID, BS.HIT_DATE, BS.VISITID, BS.HITS_HITNUMBER, BS.VISITSTARTTIME, BS.HITS_PAGE_HOSTNAME, BS.HITS_PAGE_PAGEPATH AS OLD, BS.HITS_EVENTINFO_EVENTACTION, BS.HITS_EVENTINFO_EVENTLABEL, CASE WHEN CHARINDEX('&',HITS_PAGE_PAGEPATH)>1 THEN LEFT(HITS_PAGE_PAGEPATH, CHARINDEX('&',HITS_PAGE_PAGEPATH)-1) ELSE BS.HITS_PAGE_PAGEPATH END AS HITS_PAGE_PAGEPATH FROM TEMP.HITS_CLEAN_BASE_1(NOLOCK) BS CREATE STATISTICS STAT_FULLVISITORID ON TEMP.HITS_CLEAN_BASE_2(FULLVISITORID)--(9,77,93,547 ROWS AFFECTED); FVIDS: 91,59,136 --SELECT COUNT(1) AS CNT, COUNT(DISTINCT FULLVISITORID) AS DST_FVID FROM TEMP.HITS_CLEAN_BASE_2(NOLOCK) -- CLEARNING HITS DATA FOR EXTRA ?'S IF OBJECT_ID('TEMP.HITS_CLEAN_BASE_FINAL') IS NOT NULL DROP TABLE TEMP.HITS_CLEAN_BASE_FINAL CREATE TABLE TEMP.HITS_CLEAN_BASE_FINAL WITH (DISTRIBUTION = HASH(FULLVISITORID), HEAP) AS SELECT BS.FULLVISITORID, BS.HIT_DATE, BS.VISITID, CAST(BS.HITS_HITNUMBER AS INT) HITS_HITNUMBER, BS.HITS_PAGE_HOSTNAME, BS.HITS_PAGE_PAGEPATH AS OLD, DATEADD(S,CAST(BS.VISITSTARTTIME AS BIGINT),'19700101 05:30:00:000') AS VISITSTARTTIME, CASE WHEN CHARINDEX('?',HITS_PAGE_PAGEPATH)>1 THEN LEFT(HITS_PAGE_PAGEPATH, CHARINDEX('?',HITS_PAGE_PAGEPATH)-1) ELSE BS.HITS_PAGE_PAGEPATH END AS HITS_PAGE_PAGEPATH, BS.HITS_EVENTINFO_EVENTACTION, BS.HITS_EVENTINFO_EVENTLABEL FROM TEMP.HITS_CLEAN_BASE_2(NOLOCK) BS GROUP BY BS.FULLVISITORID, BS.HIT_DATE, BS.VISITID, CAST(BS.HITS_HITNUMBER AS INT), BS.HITS_PAGE_HOSTNAME, BS.HITS_PAGE_PAGEPATH, DATEADD(S,CAST(BS.VISITSTARTTIME AS BIGINT),'19700101 05:30:00:000'), CASE WHEN CHARINDEX('?',HITS_PAGE_PAGEPATH)>1 THEN LEFT(HITS_PAGE_PAGEPATH, CHARINDEX('?',HITS_PAGE_PAGEPATH)-1) ELSE BS.HITS_PAGE_PAGEPATH END, BS.HITS_EVENTINFO_EVENTACTION, BS.HITS_EVENTINFO_EVENTLABEL CREATE INDEX FULLVISITORID ON TEMP.HITS_CLEAN_BASE_FINAL(FULLVISITORID) CREATE INDEX VISITID ON TEMP.HITS_CLEAN_BASE_FINAL(VISITID) CREATE STATISTICS STAT_FULLVISITORID ON TEMP.HITS_CLEAN_BASE_FINAL(FULLVISITORID)--(9,74,86,098 ROWS AFFECTED); FVIDS : 91,59,136 --SELECT COUNT(1) AS CNT, COUNT(DISTINCT FULLVISITORID) AS DST_FVID FROM TEMP.HITS_CLEAN_BASE_FINAL(NOLOCK)