1

Please login or register to vote for this query.

(click on this box to dismiss)

プログラマーと熱狂的なプログラマーのためのサイトに関する質問と回答

DECLARE @pscore int
DECLARE @nscore int

SET @pscore = ##PosScoreThreshold?10##
SET @nscore = ##NegScoreThreshold?-10##

DECLARE @db sysname
DECLARE @query nvarchar(max)
DECLARE c CURSOR FOR 
  SELECT Name 
    FROM sys.databases 
   WHERE database_id > 5
     AND Name LIKE '%.Meta'

CREATE TABLE #PS (Site sysname,
                  K float,
                  Id int,
                  Score int,
                  CreationDate datetime,
                  ClosedDate datetime,
                  Tags nvarchar(250))

OPEN c
FETCH NEXT FROM c INTO @db
WHILE (@@FETCH_STATUS = 0) BEGIN
  SET @query =
    '
    INSERT INTO #PS
    SELECT ''' + @db + ''', RAND(), Id, Score, CreationDate, ClosedDate, Tags
      FROM ' + QUOTENAME(@db) + '.dbo.Posts
     WHERE PostTypeId = 1
       AND CreationDate >= ''##StartDate?2015-01-01 00:00:00##''
       AND CreationDate <= ''##EndDate?2016-01-01 00:00:00##''
       AND ((Tags LIKE ''%<feature-request>%'' AND
             Tags LIKE ''%<status-%'') OR
             Tags LIKE ''%<discussion>%'')    
    '
  EXEC(@Query)
  FETCH NEXT FROM c INTO @db
END;
CLOSE c;
DEALLOCATE c; 

SELECT * FROM (SELECT TOP ##Samples?5## * FROM #PS
 WHERE Tags LIKE '%<feature-request>%' AND
       Tags LIKE '%<status-completed>%' AND
       Score >= @pscore AND
       ClosedDate IS NULL
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##Samples## * FROM #PS
 WHERE Tags LIKE '%<feature-request>%' AND
       Tags LIKE '%<status-completed>%' AND
       Score < 0 AND -- These are pretty rare so @nscore threshold usually not met.
       ClosedDate IS NULL
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##Samples## * FROM #PS
 WHERE Tags LIKE '%<feature-request>%' AND
       Tags LIKE '%<status-declined>%' AND
       Score >= @pscore AND
       ClosedDate IS NULL
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##Samples## * FROM #PS
 WHERE Tags LIKE '%<feature-request>%' AND
       Tags LIKE '%<status-declined>%' AND
       Score <= @nscore AND
       ClosedDate IS NULL
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##Samples## * FROM #PS
 WHERE Tags LIKE '%<feature-request>%' AND
       ClosedDate IS NOT NULL AND
       Score <= @nscore 
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##Samples## * FROM #PS
 WHERE Tags LIKE '%<discussion>%' AND
       Score >= @pscore AND
       ClosedDate IS NULL
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##Samples## * FROM #PS
 WHERE Tags LIKE '%<discussion>%' AND
       Score <= @nscore AND
       ClosedDate IS NULL
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##Samples## * FROM #PS
 WHERE Tags LIKE '%<discussion>%' AND
       ClosedDate IS NOT NULL AND
       Score <= @nscore
 ORDER BY K) x

view current version

edited may 24 17

created may 24 17 Jason C