sql - 计数值，连续且不在分组表上

Question

我有一个名为示例的 SQL 表，定义如下:

sampledate (datetime, 24 records per day per parameter)
parameterID (int)
value (decimal)
valid (bit, 1=valid data, 0=invalid data)

这对sampledate和parameterid是唯一的。

每个样本日期的格式为 02/02/2011 12:00，因此每个参数 ID 每天有 24 行或更少(例如，探针可能会失败或处于维护状态，并且它将输出少于 24 个样本) )。

我必须计算每个参数的平均每日值。仅当满足以下条件时，平均值才在给定日期有效:

1. 至少存在 18 个有效值
2. 存在的无效连续值不超过 5

对于给定的@参数，条件 1) 实现起来非常简单:

  SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, sampledate))) as avgdate,
         AVG(value) as avg, parameterID, 
         isValid = CASE  
           WHEN COUNT(value) > 17 THEN 1
           ELSE 0
         END 
    FROM samples
   WHERE parameterId=@parameter
GROUP BY parameterId, CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, sampledate))), valid
  HAVING valid = 1  
ORDER BY sampledate

如何添加条件 2(归结为在 24 小时范围内计算连续的 0)，可能具有最佳性能？

我们有数百万个样本，但光标速度很慢。

Answer 1

这是我的递归 CTE 解决方案，它是可参数化的:

WITH
seq_samples AS (
  SELECT
    sampledate, parameterID, value, valid,
    avgdate = CAST(FLOOR(CAST(sampledate AS float)) AS datetime),
    rownum = ROW_NUMBER() OVER (
      PARTITION BY parameterID, CAST(FLOOR(CAST(sampledate AS float)) AS datetime)
      ORDER BY sampledate)
  FROM samples
),
rec_samples AS (
  SELECT
    sampledate, parameterID, value, valid, avgdate, rownum,
    inv_seq_num = 1 - valid
  FROM seq_samples
  WHERE rownum = 1
  UNION ALL
  SELECT
    ss.sampledate, ss.parameterID, ss.value, ss.valid, ss.avgdate, ss.rownum,
    inv_seq_num = CASE ss.valid WHEN 1 THEN 0 ELSE rs.inv_seq_num + 1 END
  FROM seq_samples ss
    INNER JOIN rec_samples rs ON ss.avgdate = rs.avgdate
      AND ss.parameterID = rs.parameterID AND ss.rownum = rs.rownum + 1
)
SELECT
  avgdate,
  parameterID,
  avgvalue = AVG(value)
FROM rec_samples
GROUP BY avgdate, parameterID
HAVING SUM(CAST(valid AS int)) >= @minimal_valid_count
   AND MAX(inv_seq_num)        <= @critical_invalid_count

你的想法基本上在这里实现了。使用附加编号，该编号仅应用于无效行，并且仅因日期转换和有效值的出现而中断。最后将 MAX 应用于编号列，以查明最大数量是否未超过 @ritic_invalid_count。对于另一个参数，检查有效属性的总和显然就足够了。

所以，你来了。

<小时/>

seq_samples CTE 的编辑(将应用于原始查询的改编版本)。

seq_samples AS (
  SELECT
    *,
    rownum = ROW_NUMBER() OVER (
      PARTITION BY parameterID, avgdate
      ORDER BY sampledate)
  FROM (
    SELECT
      sampledate, parameterID, value, valid,
      avgdate = CAST(FLOOR(CAST(sampledate AS float)) AS datetime)
    FROM samples
  ) s
),

SSMS 向我展示了原始查询和修改后的查询之间显着的、几乎令人难以置信的性能差异。 (这仅基于估计执行计划中的数字。)我不知道您必须对我原来的解决方案进行哪些调整，但我希望我所见证的改进不会因此而完全丢失。