1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for users and developers of the Cardano cryptocurrency ecosystem

-- 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

fork query

edited aug 15

created aug 15 Dennis.Leeftink