0

Please login or register to vote for this query.

(click on this box to dismiss)

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

--  Chunk Start
--      The date to start at
--      input   :   date in yyyymmdd format
--      default :   today's date
--
--  Chunk Type
--      The time unit to chunk by
--      input   :   1.  Days
--                  2.  Months
--                  3.  Years
--      default :   1
--
--  Chunk Size
--      The size of each chunk
--      input   :   Group n @chunkType into one chunk
--      default :   1
--
--  Chunk Amount Type
--      The time unit to determine the size of
--      input   :   0.  Same as @chunkType
--                  1.  Days
--                  2.  Months
--                  3.  Years
--      default :   0
--
--  Chunk Amount Size
--      The amount of chunks to take in each chunk group
--      input   :   Group n @chunkAmountType into a chunk group
--      default :   100
--
--  Chunk Skip Type
--      The time unit to determine the size of
--      input   :   0.  Same as @chunkAmountType
--                  1.  Days
--                  2.  Months
--                  3.  Years
--      default :   0
--
--  Chunk Skip
--      The amount of chunks to skip between chunk groups
--      input   :   Skip n @chunkSkipType between the first of each chunk group
--      default :   12
--
--  Chunk Groups
--      The amount of chunk groups
--      input   :   The amount of chunk groups desired
--      default :   0
--
--  Date Normalize
--      The normalized graph view of the data
--      input   :   2.  Month view
--                  3.  Year view
--                  4.  Infinate view
--      default :   4
--
--  Good Values
--      Quarterly (days)
--          ChunkStart      20210400
--          ChunkType       1
--          ChunkAmountType 2
--          ChunkSkipType   0
--          ChunkSize       1
--          ChunkAmountSize 6
--          ChunkSkip       12
--          ChunkGroups     2
--
--      Quarterly (months)
--          ChunkStart      20210100
--          ChunkSize       7
--          ChunkAmountSize 3
--          ChunkSkip       12
--          ChunkGroups     5
--          ChunkType       1
--          ChunkAmountType 2
--          ChunkSkipType   0
--          dateNormalize   3
--
--      Continuous (days)
--          ChunkStart      20210400
--          ChunkType       1
--          ChunkAmountType 0
--          ChunkSkipType   0
--          ChunkSize       1
--          ChunkAmountSize 100
--          ChunkSkip       12
--          ChunkGroups     1
--
--      Continuous (months)
--          ChunkStart      20210400
--          ChunkType       2
--          ChunkAmountType 0
--          ChunkSkipType   0
--          ChunkSize       1
--          ChunkAmountSize 100
--          ChunkSkip       12
--          ChunkGroups     1

DECLARE @_chunkStart AS int = ##ChunkStart:int?0##;
  DECLARE @chunkStart AS date  = DateAdd(
    day,
    1,
    CASE
      WHEN @_chunkStart = 0 THEN Convert(date, CURRENT_TIMESTAMP)
      ELSE CASE
    WHEN @_chunkStart % 100 = 0 THEN DateAdd(day, -1, cast(cast(@_chunkStart + 1 AS varchar(255)) AS date))
    ELSE cast(cast(@_chunkStart AS varchar(255)) AS date)
  END
    END
  );
DECLARE @chunkSize AS int   = ##ChunkSize:int?1##;
DECLARE @chunkAmountSize AS int = ##ChunkAmountSize:int?100##;

    DECLARE @_chunkSkip AS int = ##ChunkSkip:int?0##;
    DECLARE @chunkSkip AS int     = CASE
      WHEN @_chunkSkip = 0 THEN @chunkAmountSize
      ELSE @_chunkSkip
    END;
  
DECLARE @chunkGroups AS int = ##ChunkGroups:int?1##;

DECLARE @chunkType AS int = ##ChunkType:int?1##;

    DECLARE @_chunkAmountType AS int = ##ChunkAmountType:int?0##;
    DECLARE @chunkAmountType AS int     = CASE
      WHEN @_chunkAmountType = 0 THEN @chunkType
      ELSE @_chunkAmountType
    END;
  

    DECLARE @_chunkSkipType AS int = ##ChunkSkipType:int?0##;
    DECLARE @chunkSkipType AS int     = CASE
      WHEN @_chunkSkipType = 0 THEN @chunkAmountType
      ELSE @_chunkSkipType
    END;
  
DECLARE @dateNormalize AS int = ##DateNormalize:int?4##;

SELECT
    ROW_NUMBER() OVER (ORDER BY Id Asc) - 1 AS Id
  INTO #ChunkNumbers
  FROM Posts
  ;

  
  
  WITH
  _ChunksEnd AS (
    SELECT
      n.Id AS Id,
      
  CASE
    WHEN @chunkSkipType = 1 THEN DateAdd(day,   -n.Id * @chunkSkip, @chunkStart)
    WHEN @chunkSkipType = 2 THEN DateAdd(month, -n.Id * @chunkSkip, @chunkStart)
    WHEN @chunkSkipType = 3 THEN DateAdd(year,  -n.Id * @chunkSkip, @chunkStart)
    ELSE Null
  END
 AS ChunkEnd
     FROM #ChunkNumbers AS n
    WHERE n.Id < @chunkGroups
  ),
  _ChunksStart AS (
    SELECT
      c.Id,
      
  CASE
    WHEN @chunkAmountType = 1 THEN DateAdd(day,   -@chunkAmountSize, c.ChunkEnd)
    WHEN @chunkAmountType = 2 THEN DateAdd(month, -@chunkAmountSize, c.ChunkEnd)
    WHEN @chunkAmountType = 3 THEN DateAdd(year,  -@chunkAmountSize, c.ChunkEnd)
    ELSE Null
  END
 AS ChunkStart,
      c.ChunkEnd,
      DateAdd(day, -1, c.ChunkEnd) AS _ChunkEnd
    FROM _ChunksEnd AS c
  ),
  _ChunksGroups AS (
    SELECT
      c.Id,
      CASE
      WHEN year(c.ChunkStart) != year(c._ChunkEnd) THEN CASE
        WHEN
          day(c.ChunkStart) = day(c._ChunkEnd)
          AND month(c.ChunkStart) = month(c._ChunkEnd)
        THEN 
  
  
    cast(day(c.ChunkStart) AS varchar) + CASE WHEN day(c.ChunkStart) % 10 = 1 THEN 'st' WHEN day(c.ChunkStart) % 10 = 2 THEN 'nd' WHEN day(c.ChunkStart) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c.ChunkStart) = 1 THEN 'Jan' WHEN month(c.ChunkStart) = 2 THEN 'Feb' WHEN month(c.ChunkStart) = 3 THEN 'Mar' WHEN month(c.ChunkStart) = 4 THEN 'Apr' WHEN month(c.ChunkStart) = 5 THEN 'May' WHEN month(c.ChunkStart) = 6 THEN 'Jun' WHEN month(c.ChunkStart) = 7 THEN 'Jul' WHEN month(c.ChunkStart) = 8 THEN 'Aug' WHEN month(c.ChunkStart) = 9 THEN 'Sep' WHEN month(c.ChunkStart) = 10 THEN 'Oct' WHEN month(c.ChunkStart) = 11 THEN 'Nov' WHEN month(c.ChunkStart) = 12 THEN 'Dec' END
   + ', ' + 
  
    cast(year(c.ChunkStart) AS varchar)
   + ' - ' + 
  
    cast(year(c.ChunkStart) AS varchar)
  
        ELSE 
  
  
    cast(day(c.ChunkStart) AS varchar) + CASE WHEN day(c.ChunkStart) % 10 = 1 THEN 'st' WHEN day(c.ChunkStart) % 10 = 2 THEN 'nd' WHEN day(c.ChunkStart) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c.ChunkStart) = 1 THEN 'Jan' WHEN month(c.ChunkStart) = 2 THEN 'Feb' WHEN month(c.ChunkStart) = 3 THEN 'Mar' WHEN month(c.ChunkStart) = 4 THEN 'Apr' WHEN month(c.ChunkStart) = 5 THEN 'May' WHEN month(c.ChunkStart) = 6 THEN 'Jun' WHEN month(c.ChunkStart) = 7 THEN 'Jul' WHEN month(c.ChunkStart) = 8 THEN 'Aug' WHEN month(c.ChunkStart) = 9 THEN 'Sep' WHEN month(c.ChunkStart) = 10 THEN 'Oct' WHEN month(c.ChunkStart) = 11 THEN 'Nov' WHEN month(c.ChunkStart) = 12 THEN 'Dec' END + ' ' + cast(year(c.ChunkStart) AS varchar)
   + ' - ' + 
  
    cast(day(c._ChunkEnd) AS varchar) + CASE WHEN day(c._ChunkEnd) % 10 = 1 THEN 'st' WHEN day(c._ChunkEnd) % 10 = 2 THEN 'nd' WHEN day(c._ChunkEnd) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c._ChunkEnd) = 1 THEN 'Jan' WHEN month(c._ChunkEnd) = 2 THEN 'Feb' WHEN month(c._ChunkEnd) = 3 THEN 'Mar' WHEN month(c._ChunkEnd) = 4 THEN 'Apr' WHEN month(c._ChunkEnd) = 5 THEN 'May' WHEN month(c._ChunkEnd) = 6 THEN 'Jun' WHEN month(c._ChunkEnd) = 7 THEN 'Jul' WHEN month(c._ChunkEnd) = 8 THEN 'Aug' WHEN month(c._ChunkEnd) = 9 THEN 'Sep' WHEN month(c._ChunkEnd) = 10 THEN 'Oct' WHEN month(c._ChunkEnd) = 11 THEN 'Nov' WHEN month(c._ChunkEnd) = 12 THEN 'Dec' END + ' ' + cast(year(c._ChunkEnd) AS varchar)
  
      END
      WHEN month(c.ChunkStart) != month(c._ChunkEnd) THEN 
  
  
    cast(day(c.ChunkStart) AS varchar) + CASE WHEN day(c.ChunkStart) % 10 = 1 THEN 'st' WHEN day(c.ChunkStart) % 10 = 2 THEN 'nd' WHEN day(c.ChunkStart) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c.ChunkStart) = 1 THEN 'Jan' WHEN month(c.ChunkStart) = 2 THEN 'Feb' WHEN month(c.ChunkStart) = 3 THEN 'Mar' WHEN month(c.ChunkStart) = 4 THEN 'Apr' WHEN month(c.ChunkStart) = 5 THEN 'May' WHEN month(c.ChunkStart) = 6 THEN 'Jun' WHEN month(c.ChunkStart) = 7 THEN 'Jul' WHEN month(c.ChunkStart) = 8 THEN 'Aug' WHEN month(c.ChunkStart) = 9 THEN 'Sep' WHEN month(c.ChunkStart) = 10 THEN 'Oct' WHEN month(c.ChunkStart) = 11 THEN 'Nov' WHEN month(c.ChunkStart) = 12 THEN 'Dec' END
   + ' - ' + 
  
    cast(day(c._ChunkEnd) AS varchar) + CASE WHEN day(c._ChunkEnd) % 10 = 1 THEN 'st' WHEN day(c._ChunkEnd) % 10 = 2 THEN 'nd' WHEN day(c._ChunkEnd) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c._ChunkEnd) = 1 THEN 'Jan' WHEN month(c._ChunkEnd) = 2 THEN 'Feb' WHEN month(c._ChunkEnd) = 3 THEN 'Mar' WHEN month(c._ChunkEnd) = 4 THEN 'Apr' WHEN month(c._ChunkEnd) = 5 THEN 'May' WHEN month(c._ChunkEnd) = 6 THEN 'Jun' WHEN month(c._ChunkEnd) = 7 THEN 'Jul' WHEN month(c._ChunkEnd) = 8 THEN 'Aug' WHEN month(c._ChunkEnd) = 9 THEN 'Sep' WHEN month(c._ChunkEnd) = 10 THEN 'Oct' WHEN month(c._ChunkEnd) = 11 THEN 'Nov' WHEN month(c._ChunkEnd) = 12 THEN 'Dec' END
   + ', ' + 
  
    cast(year(c.ChunkStart) AS varchar)
  
      WHEN day(c.ChunkStart) != day(c._ChunkEnd) THEN 
  
  
    cast(day(c.ChunkStart) AS varchar) + CASE WHEN day(c.ChunkStart) % 10 = 1 THEN 'st' WHEN day(c.ChunkStart) % 10 = 2 THEN 'nd' WHEN day(c.ChunkStart) % 10 = 3 THEN 'rd' ELSE 'th' END
   + ' to ' + 
  
    cast(day(c._ChunkEnd) AS varchar) + CASE WHEN day(c._ChunkEnd) % 10 = 1 THEN 'st' WHEN day(c._ChunkEnd) % 10 = 2 THEN 'nd' WHEN day(c._ChunkEnd) % 10 = 3 THEN 'rd' ELSE 'th' END
   + ' ' + 
  
    CASE WHEN month(c.ChunkStart) = 1 THEN 'Jan' WHEN month(c.ChunkStart) = 2 THEN 'Feb' WHEN month(c.ChunkStart) = 3 THEN 'Mar' WHEN month(c.ChunkStart) = 4 THEN 'Apr' WHEN month(c.ChunkStart) = 5 THEN 'May' WHEN month(c.ChunkStart) = 6 THEN 'Jun' WHEN month(c.ChunkStart) = 7 THEN 'Jul' WHEN month(c.ChunkStart) = 8 THEN 'Aug' WHEN month(c.ChunkStart) = 9 THEN 'Sep' WHEN month(c.ChunkStart) = 10 THEN 'Oct' WHEN month(c.ChunkStart) = 11 THEN 'Nov' WHEN month(c.ChunkStart) = 12 THEN 'Dec' END + ' ' + cast(year(c.ChunkStart) AS varchar)
  
      ELSE 
  
    cast(day(c.ChunkStart) AS varchar) + CASE WHEN day(c.ChunkStart) % 10 = 1 THEN 'st' WHEN day(c.ChunkStart) % 10 = 2 THEN 'nd' WHEN day(c.ChunkStart) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c.ChunkStart) = 1 THEN 'Jan' WHEN month(c.ChunkStart) = 2 THEN 'Feb' WHEN month(c.ChunkStart) = 3 THEN 'Mar' WHEN month(c.ChunkStart) = 4 THEN 'Apr' WHEN month(c.ChunkStart) = 5 THEN 'May' WHEN month(c.ChunkStart) = 6 THEN 'Jun' WHEN month(c.ChunkStart) = 7 THEN 'Jul' WHEN month(c.ChunkStart) = 8 THEN 'Aug' WHEN month(c.ChunkStart) = 9 THEN 'Sep' WHEN month(c.ChunkStart) = 10 THEN 'Oct' WHEN month(c.ChunkStart) = 11 THEN 'Nov' WHEN month(c.ChunkStart) = 12 THEN 'Dec' END + ' ' + cast(year(c.ChunkStart) AS varchar)
  
    END AS Name,
      c.ChunkStart,
      c.ChunkEnd,
      CAST(CASE
    WHEN @chunkType = 1 THEN DateDiff(day,   c.ChunkStart, c.ChunkEnd)
    WHEN @chunkType = 2 THEN DateDiff(month, c.ChunkStart, c.ChunkEnd)
    WHEN @chunkType = 3 THEN DateDiff(year,  c.ChunkStart, c.ChunkEnd)
    ELSE Null
  END AS FLOAT) / CAST(@chunkSize AS FLOAT) AS Amount
    FROM _ChunksStart AS c
  )

  SELECT
    c.*
  INTO #ChunkGroups
  FROM _ChunksGroups AS c
  ;

  
  WITH
  _ChunksChunk AS (
    SELECT
      n.Id AS ChunkIndex,
      g.Id AS ChunkGroupId,
      
  CASE
    WHEN @chunkType = 1 THEN DateAdd(day,   (n.Id + 0) * @chunkSize, g.ChunkStart)
    WHEN @chunkType = 2 THEN DateAdd(month, (n.Id + 0) * @chunkSize, g.ChunkStart)
    WHEN @chunkType = 3 THEN DateAdd(year,  (n.Id + 0) * @chunkSize, g.ChunkStart)
    ELSE Null
  END
 AS StartDate,
      
  CASE
    WHEN @chunkType = 1 THEN DateAdd(day,   (n.Id + 1) * @chunkSize, g.ChunkStart)
    WHEN @chunkType = 2 THEN DateAdd(month, (n.Id + 1) * @chunkSize, g.ChunkStart)
    WHEN @chunkType = 3 THEN DateAdd(year,  (n.Id + 1) * @chunkSize, g.ChunkStart)
    ELSE Null
  END
 AS EndDate
          FROM #ChunkGroups AS g
    INNER JOIN #ChunkNumbers AS n
            ON n.Id < g.Amount
  ),
  _ChunksCorrected AS (
    SELECT
      c.ChunkIndex,
      c.ChunkGroupId,
      c.StartDate,
      CASE
    WHEN year(c.EndDate) * 10000 + month(c.EndDate) * 100 + day(c.EndDate) <= year(g.ChunkEnd) * 10000 + month(g.ChunkEnd) * 100 + day(g.ChunkEnd) THEN c.EndDate
    ELSE g.ChunkEnd
  END AS EndDate
          FROM _ChunksChunk AS c
    INNER JOIN #ChunkGroups AS g
            ON c.ChunkGroupId = g.Id
  )

  SELECT
    c.*
  INTO #Chunks
  FROM _ChunksCorrected AS c
  ;

SELECT
  s.Id AS Id,
  1 AS Value,
  1 AS Amount,
  s.CreationDate AS Date,
  s.OwnerUserId AS "User",
  c.ChunkIndex AS ChunkIndex,
  c.ChunkGroupId AS ChunkGroupId
      INTO #Answers
      FROM Posts AS s
INNER JOIN #Chunks AS c
        ON c.StartDate <= s.CreationDate
       AND s.CreationDate < c.EndDate
     WHERE s.PostTypeId = 2
;
SELECT
  s.Id AS Id,
  1 AS Value,
  1 AS Amount,
  s.CreationDate AS Date,
  s.OwnerUserId AS "User",
  c.ChunkIndex AS ChunkIndex,
  c.ChunkGroupId AS ChunkGroupId
      INTO #Questions
      FROM Posts AS s
INNER JOIN #Chunks AS c
        ON c.StartDate <= s.CreationDate
       AND s.CreationDate < c.EndDate
     WHERE s.PostTypeId = 1
;

SELECT
    ChunkIndex AS ChunkIndex,
    ChunkGroupId AS ChunkGroupId,
    sum(Value) AS Value,
    sum(Amount) AS Amount
      INTO #ChunkAnswers
      FROM #Answers
  GROUP BY ChunkGroupId, ChunkIndex
SELECT
    ChunkIndex AS ChunkIndex,
    ChunkGroupId AS ChunkGroupId,
    sum(Value) AS Value,
    sum(Amount) AS Amount
      INTO #ChunkQuestions
      FROM #Questions
  GROUP BY ChunkGroupId, ChunkIndex




SELECT
  a.ChunkIndex,
  a.ChunkGroupId,
  cast(a.Value AS float) / cast(c.Value AS float) AS Value
      INTO #Metric
      FROM #ChunkAnswers AS a
INNER JOIN #ChunkQuestions AS c
        ON a.ChunkGroupId = c.ChunkGroupId
       AND a.ChunkIndex = c.ChunkIndex
  ORDER BY a.ChunkGroupId ASC, a.ChunkIndex ASC
;


WITH

  
  
  
  _CleanDates AS (
    SELECT
      s.Value AS Value,
      g.Name AS Name,
      CASE
        WHEN @dateNormalize = 3 THEN 
  CASE
    WHEN @chunkType = 1 THEN DateAdd(day,   s.ChunkIndex * @chunkSize, datefromparts(2000, month(g.ChunkStart), day(g.ChunkStart)))
    WHEN @chunkType = 2 THEN DateAdd(month, s.ChunkIndex * @chunkSize, datefromparts(2000, month(g.ChunkStart), day(g.ChunkStart)))
    WHEN @chunkType = 3 THEN DateAdd(year,  s.ChunkIndex * @chunkSize, datefromparts(2000, month(g.ChunkStart), day(g.ChunkStart)))
    ELSE Null
  END

        WHEN @dateNormalize = 4 THEN c.StartDate
        ELSE Null
      END AS Date,
      c.StartDate AS "RawDate"
          FROM #Metric AS s
    INNER JOIN #ChunkGroups AS g
            ON s.ChunkGroupId = g.Id
    INNER JOIN #Chunks AS c
            ON s.ChunkIndex = c.ChunkIndex
          AND s.ChunkGroupId = c.ChunkGroupId
  ),
  _MetricClean AS (
    SELECT
      c.Value AS Value,
      c.Name AS Name,
      CASE
        WHEN @dateNormalize = 3 THEN DateAdd(day,   -datepart(dayofyear, c.Date) % @chunkSize, c.Date)
        WHEN @dateNormalize = 4 THEN c.Date
        ELSE Null
      END AS Date
    FROM _CleanDates AS c
  ),

  _CleanExit AS (
    SELECT
      *
    FROM _MetricClean
  )

  SELECT
    c.*
  INTO #MetricClean
  FROM _CleanExit AS c
  ;

DECLARE @pivot AS nvarchar(max) = STUFF(
    (
      SELECT
        ',' + QUOTENAME(Name)
          FROM #ChunkGroups
      ORDER BY Id ASC
      FOR XML PATH(''), TYPE
    ).value(
      '.',
      'NVARCHAR(MAX)'
    ),
    1,
    1,
    ''
  );
  PRINT @pivot;
  DECLARE @query AS nvarchar(max) = 'SELECT * FROM #MetricClean PIVOT ( sum(Value) FOR Name IN (' + @pivot + ') ) AS Pivoted';
  execute(@query);

fork query

edited mar 2

created feb 28 anonymous