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 uniqueidentifier,
                  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 + ''', NEWID(), 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>%'' AND
             Tags NOT LIKE ''%<feature-request>%''))    
       AND Tags NOT LIKE ''%<bug>%''
    '
  EXEC(@Query)
  FETCH NEXT FROM c INTO @db
END;
CLOSE c;
DEALLOCATE c; 

SELECT * FROM (SELECT TOP ##SamplesX2?10## 'poc' c, * 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?5## 'noc' c, * 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## 'pon' c, * 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## 'non' c, * 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## 'nc.' c, * 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 ##SamplesX2## 'pod' c, * FROM #PS
 WHERE Tags LIKE '%<discussion>%' AND
       Score >= @pscore AND
       ClosedDate IS NULL
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##Samples## 'nod' c, * FROM #PS
 WHERE Tags LIKE '%<discussion>%' AND
       Score <= @nscore AND
       ClosedDate IS NULL
 ORDER BY K) x
UNION ALL SELECT * FROM (SELECT TOP ##Samples## 'ncd' c, * 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