Mediana de Tareas de Revisión por hora en Stack Overflow en español (median review tasks by hour)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Mediana y media de cantidad de tareas de revisión por hora (a partir de días), para los últimos N días.

Stack Overflow en español

Preguntas y respuestas para programadores y profesionales de la informática

WITH PorDia AS (
        select --Revisiones por día
               --Nota: no sería preciso si algún día no hubiese revisiones de ninguna cola
               CreationDate Fecha,
               SUM(case when ReviewTaskTypeId = 4 then 1 else 0 end) Primeras,
               SUM(case when ReviewTaskTypeId = 1 then 1 else 0 end) Ediciones,
               SUM(case when ReviewTaskTypeId = 2 then 1 else 0 end) Cierre,
               SUM(case when ReviewTaskTypeId = 3 then 1 else 0 end) Calidad,
               SUM(case when ReviewTaskTypeId = 5 then 1 else 0 end) Tardias,
               SUM(case when ReviewTaskTypeId = 6 then 1 else 0 end) Reapertura,
               SUM(case when ReviewTaskTypeId = 10 then 1 else 0 end) Triage,
               SUM(case when ReviewTaskTypeId = 11 then 1 else 0 end) Helper,
               count(*) Total,
               SUM(case when ReviewTaskTypeId = 2 then 0 else 1 end) TotalSinCierre
          from ReviewTasks
               --En los últimos N días
         where CreationDate >= dateadd(day, -##CantDias:int?30##, dateadd(week, datediff(day, '20000109', GETDATE()) / 7, '20000109'))
               --Excepto la última semana incompleta
           and CreationDate < dateadd(week, datediff(day, '20000109', GETDATE()) / 7, '20000109')
         group by CreationDate
),

MedianasPorHora AS (
        select 'Mediana' [Por Hora],
               cast((SELECT MIN(Fecha) FROM PorDia) as varchar(20)) Periodo,
               
               --Median from https://stackoverflow.com/a/2026609/5290909
               round(cast((SELECT MAX(Primeras) FROM(SELECT TOP 50 PERCENT Primeras FROM PorDia ORDER BY Primeras) AS BottomHalf)
               +(SELECT MIN(Primeras) FROM(SELECT TOP 50 PERCENT Primeras FROM PorDia ORDER BY Primeras DESC) AS TopHalf)
               as decimal(10,2)) / 2 / 24, 2) Primeras,
               
               round(cast((SELECT MAX(Ediciones) FROM(SELECT TOP 50 PERCENT Ediciones FROM PorDia ORDER BY Ediciones) AS BottomHalf)
               +(SELECT MIN(Ediciones) FROM(SELECT TOP 50 PERCENT Ediciones FROM PorDia ORDER BY Ediciones DESC) AS TopHalf)
               as decimal(10,2)) / 2 / 24, 2) Ediciones,
               
               round(cast((SELECT MAX(Cierre) FROM(SELECT TOP 50 PERCENT Cierre FROM PorDia ORDER BY Cierre) AS BottomHalf)
               +(SELECT MIN(Cierre) FROM(SELECT TOP 50 PERCENT Cierre FROM PorDia ORDER BY Cierre DESC) AS TopHalf)
               as decimal(10,2)) / 2 / 24, 2) Cierre,
               
               round(cast((SELECT MAX(Calidad) FROM(SELECT TOP 50 PERCENT Calidad FROM PorDia ORDER BY Calidad) AS BottomHalf)
               +(SELECT MIN(Calidad) FROM(SELECT TOP 50 PERCENT Calidad FROM PorDia ORDER BY Calidad DESC) AS TopHalf)
               as decimal(10,2)) / 2 / 24, 2) Calidad,
               
               round(cast((SELECT MAX(Tardias) FROM(SELECT TOP 50 PERCENT Tardias FROM PorDia ORDER BY Tardias) AS BottomHalf)
               +(SELECT MIN(Tardias) FROM(SELECT TOP 50 PERCENT Tardias FROM PorDia ORDER BY Tardias DESC) AS TopHalf)
               as decimal(10,2)) / 2 / 24, 2) Tardias,
               
               round(cast((SELECT MAX(Reapertura) FROM(SELECT TOP 50 PERCENT Reapertura FROM PorDia ORDER BY Reapertura) AS BottomHalf)
               +(SELECT MIN(Reapertura) FROM(SELECT TOP 50 PERCENT Reapertura FROM PorDia ORDER BY Reapertura DESC) AS TopHalf)
               as decimal(10,2)) / 2 / 24, 2) Reapertura,
               
               round(cast((SELECT MAX(Triage) FROM(SELECT TOP 50 PERCENT Triage FROM PorDia ORDER BY Triage) AS BottomHalf)
               +(SELECT MIN(Triage) FROM(SELECT TOP 50 PERCENT Triage FROM PorDia ORDER BY Triage DESC) AS TopHalf)
               as decimal(10,2)) / 2 / 24, 2) Triage,
               
               round(cast((SELECT MAX(Helper) FROM(SELECT TOP 50 PERCENT Helper FROM PorDia ORDER BY Helper) AS BottomHalf)
               +(SELECT MIN(Helper) FROM(SELECT TOP 50 PERCENT Helper FROM PorDia ORDER BY Helper DESC) AS Tolf)
               as decimal(10,2)) / 2 / 24, 2) Helper,
               
               round(cast((SELECT MAX(Total) FROM(SELECT TOP 50 PERCENT Total FROM PorDia ORDER BY Total) AS BottomHalf)
               +(SELECT MIN(Total) FROM(SELECT TOP 50 PERCENT Total FROM PorDia ORDER BY Total DESC) AS TopHalf)
               as decimal(10,2)) / 2 / 24, 2) Total,
               
               round(cast((SELECT MAX(TotalSinCierre) FROM(SELECT TOP 50 PERCENT TotalSinCierre FROM PorDia ORDER BY TotalSinCierre) AS BottomHalf)
               +(SELECT MIN(TotalSinCierre) FROM(SELECT TOP 50 PERCENT TotalSinCierre FROM PorDia ORDER BY TotalSinCierre DESC) AS TopHalf)
               as decimal(10,2)) / 2 / 24, 2) TotalSinCierre
),

MediasPorHora AS (
      select 'Media' [Por Hora],
             cast((SELECT MAX(Fecha) FROM PorDia) as varchar(20)) Periodo,
             round(cast(AVG(Primeras) as decimal(10,2)) / 24,2) Primeras,
             round(cast(AVG(Ediciones) as decimal(10,2)) / 24,2) Ediciones,
             round(cast(AVG(Cierre) as decimal(10,2)) / 24,2) Cierre,
             round(cast(AVG(Calidad) as decimal(10,2)) / 24,2) Calidad,
             round(cast(AVG(Tardias) as decimal(10,2)) / 24,2) Tardias,
             round(cast(AVG(Reapertura) as decimal(10,2)) / 24,2) Reapertura,
             round(cast(AVG(Triage) as decimal(10,2)) / 24,2) Triage,
             round(cast(AVG(Helper) as decimal(10,2)) / 24,2) Helper,
             round(cast(AVG(Total) as decimal(10,2)) / 24,2) Total,
             round(cast(AVG(TotalSinCierre) as decimal(10,2)) / 24,2) TotalSinCierre
        from PorDia
)

Select * 
  from MedianasPorHora
union all 
Select *
  from MediasPorHora

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results