Q&A for designers and implementers of computer programming languages
-- stack(24.04.22)exchange(cross)section(complex)interpolation -- query template must match #Output schema -- set user parameters DECLARE @past int = ##Past:int?16##; DECLARE @tops int = ##TopS:int?36##; DECLARE @topk int = ##TopK:int?192##; DECLARE @near int = ##Near:string?1##; DECLARE @bins nvarchar(10) = ##Bins:string?month##; DECLARE @bias float = ##Bias:float?0.15## DECLARE @wild nvarchar(35) = ##Wildcard:string?%-api##; -- setup user tags DECLARE @tag1 nvarchar(35) = ##Tag1:string?..## DECLARE @tag2 nvarchar(35) = ##Tag2:string?..## DECLARE @tag3 nvarchar(35) = ##Tag3:string?..## DECLARE @tag4 nvarchar(35) = ##Tag4:string?..## -- insert tags of interest create table #tags (pos int, tag nvarchar(35) COLLATE SQL_Latin1_General_CP1_CS_AS); insert into #tags (pos, tag) values (1, @tag1), (2, @tag2), (3, @tag3), (4, @tag4), (0, @wild); -- template query DECLARE @template varchar(max) = ' DECLARE @past int = {Y}; -- retrieve tagIds CREATE TABLE #target ( Pos int,TagId int, TagName nvarchar(35) COLLATE SQL_Latin1_General_CP1_CS_AS ); INSERT INTO #target (Pos, TagId, TagName) SELECT top {S} Pos, Id as TagId, TagName from [{dbname}].dbo.tags tags left join #tags on #tags.tag = tags.TagName WHERE TagName in (Select tag from #tags where pos > 0 and tag != ''..'') or Tagname like (Select tag from #tags where pos = 0 and len(tag)>1) order by tags.Count desc -- define date extent DECLARE @After smalldatetime, @Today smalldatetime; SET @Today = GETDATE(); SET @After = DATEADD(year, @past * -1, GETDATE()); -- run main query with main as ( SELECT pos, #target.TagName as tag, DATEADD({B}, DATEDIFF({B}, 0, CreationDate) / {N}, 0) AS date, COUNT(Posts.Id) as mag FROM #target INNER JOIN [{dbname}].dbo.PostTags PostTags ON PostTags.TagId = #target.TagId INNER JOIN [{dbname}].dbo.Posts Posts ON Posts.Id = PostTags.PostId WHERE Posts.CreationDate > @After AND Posts.CreationDate < @Today GROUP BY #target.TagName,pos, DATEADD({B}, DATEDIFF({B}, 0, CreationDate) / {N}, 0) ) -- final query must match #Output select ''{dbName}'' as site, date, tag, mag from main order by pos, date '; -- create data schemas create table #DbList ( dbname varchar(max) ); create table #KeyMap ( oldKey varchar(max), newKey varchar(max) ); create TABLE #Queries ( query varchar(max) ); create TABLE #Output ( site varchar(256), date smalldatetime, tag varchar(256), mag int ); -- populate database table INSERT INTO #Dblist (dbname) SELECT top 200 name FROM sysdatabases WHERE (name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange') AND NOT name LIKE '%.Meta') OR name = 'StackExchange.Meta'; -- populate string substitutions INSERT INTO #KeyMap (oldKey, newKey) VALUES ('TEMPLATE', @template), ('{N}',str(@near) + '*' + str(@near)), ('{Y}', str(@past)), ('{S}',str(@tops)), ('{B}',@bins); -- define interpolation function GO CREATE PROCEDURE #interpolate @target NVARCHAR(MAX) AS BEGIN DECLARE @Old VARCHAR(max), @New VARCHAR(max); DECLARE @ProcessedString VARCHAR(MAX) = @target; DECLARE KeyValueCursor CURSOR FOR SELECT oldKey, newKey FROM #KeyMap WHERE oldKey != 'TEMPLATE'; OPEN KeyValueCursor; FETCH NEXT FROM KeyValueCursor INTO @Old, @New; WHILE @@FETCH_STATUS = 0 BEGIN SET @ProcessedString = REPLACE(@ProcessedString, @Old, @New); FETCH NEXT FROM KeyValueCursor INTO @Old, @New; END; CLOSE KeyValueCursor; DEALLOCATE KeyValueCursor; SELECT @ProcessedString AS ProcessedString; END; go -- iterate over #DbList and pop/push dbname into #KeyMap -- accumulate interpolated @source/TEMPLATE into #Output DECLARE dbcursor CURSOR FOR SELECT * FROM #DbList; declare @query varchar(max); declare @dbslot varchar(max); declare @source varchar(max); set @source = (select newKey from #KeyMap where oldKey = 'TEMPLATE'); OPEN dbcursor; FETCH NEXT FROM dbcursor INTO @dbslot; INSERT INTO #KeyMap (oldKey,newKey) select '{dbname}',@dbslot WHILE @@FETCH_STATUS = 0 BEGIN -- interpolate target query from source INSERT INTO #Queries -- push EXEC #interpolate @target = @source; -- retrieve and execute dynamic query set @query = (select query from #Queries); INSERT INTO #Output EXEC (@query); -- query cycle clean-up DELETE top(1) FROM #Queries; -- pop DELETE FROM #KeyMap where newKey = @dbslot; -- load next cycle into map FETCH NEXT FROM dbcursor INTO @dbslot; INSERT INTO #KeyMap (oldKey,newKey) select '{dbname}',@dbslot END CLOSE dbcursor; -- hoist topk user variable declare @topk int = ##TopK##; declare @bias float = ##Bias##; -- post process results with data as( select site,date,tag,sum(mag) mag from #Output group by site,date,tag ) select * from data order by tag,site,date