选择大量行并按时间顺序缩放选择的有效方法？

Question

我每5分钟在一个表中插入数据，列中包含时间戳和数据。我希望根据给定的时间范围选择数据，并根据性能和时间顺序适当省略数据，以便查询返回的最大值约为32。

例如，我有2周的数据，或4032条5分钟分隔的记录。我想从头到尾进行选择，将结果集减少到32条记录，但按时间顺序设置记录集比例，以便32条记录中的每个条目在时间上尽可能相等，同时保持边缘记录(集合中的开始和结束记录)不变。

我的代码可以抓取大量的集合，并通过计算的跳过间隔对它们进行缩放，根据需要删除记录并执行边缘检查。我想知道是否有更快的方法在查询中而不是服务器代码中执行此操作。我使用的是MySQL，但我也接受MsSQL的回答。

谢谢。

Answer 1

因此，下面这些内容中，两个日期是输入，5分钟范围和范围内的32个样本：

SELECT rownum
  FROM (SELECT @row := @row +1 AS rownum
              ,@sampleRate AS sampleRate
          FROM (SELECT @row := 0
                      ,@sampleRate := TIMESTAMPDIFF(MINUTE,'2011-12-01 00:00:00','2011-12-15 00:00:00') / 5 / 32 ) r
              ,clientpc
         ) ranked
WHERE rownum % @sampleRate = 1

Answer 2

好吧，不要说我没有警告你(根据上面的评论部分)。这可能都可以在一个丑陋的大型查询中完成，但这样就更难理解了，所以我将其分解为几个步骤。

首先，设置一些变量：

DECLARE
  @Items  real  = 32   -- How many items you wish to display
 ,@From   int = 16000  --  Low range delimiter on your target data set
 ,@Thru   int = 17500  --  High range delimiter on your target data set
 ,@Total  real         --  Used to store how many items are actually in the target range

简短的测试表明，如果@Items小于2或大于@Total的某个较大倍数，则会失败。需要错误处理或输入测试。我使用real数据类型，因此除法生成的是十进制值，而不是截断的整数；一定要用整数值来设置它们，否则我不知道会发生什么。

下一位创建一个"Tally“表，或”数字表“。在这里，我将其设置为256，因为32似乎是最大值。(这段特定的代码非常迟钝，但它可以在极短的时间内生成数百万行数据，所以每当我需要这样的东西时，我都会对其进行剪切粘贴。)

CREATE TABLE #Tally (Num  int  not null)

--  "Table of numbers" data generator, as per Itzik Ben-Gan (from multiple sources)
--  Modified to generate 1 through 256
;WITH
  L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
  L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
  L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
  L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
  num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L3)
 insert #Tally (Num)
  select N FROM num

获取目标数据集中的行数：

SELECT @Total = count(*)
 from Time
 where TimeId between @From and @Thru

在集合中。这将处理重复的值。

SELECT
   row_number() over (order by TimeId) Ranking
  ,TimeId
 from Time
 where TimeId between @From and @Thru

另一个审阅查询。这将返回标识最终集合的“断点”的数字集合。例如，如果你有30个项目，想要7，这将产生{5，10，15，20，25，30}；与1结合，它就是你想要的7(如果我没有弄错的话)。

SELECT distinct ceiling((Num - 1) * @Total / (@Items - 1)) from #Tally

下面是包含上述两个查询的主要部分。基本上，取第一个查询中的所有内容，其中它的排名/位置与第二个查询中标识的“断点”相同。我在第一个项目中加入了OR，因为这比试图在数学上填入它要简单得多。

SELECT xx.Ranking, xx.TimeId
 from (select
          row_number() over (order by TimeId) Ranking
         ,TimeId
        from Time
        where TimeId between @From and @Thru) xx
 where Ranking in (select distinct ceiling((Num - 1) * @Total / (@Items - 1)) from #Tally)
  or Ranking = 1

正如我所说的，它过于复杂，对于某些输入可能不起作用--但它的运行速度可能比过程替代方法更快。

Answer 3

好吧，我想出了这个程序，任何清理工作都很感谢。在做了一些盲目调试之后，它就像我想要的那样工作了。时间存储为UTC时间戳。

    DELIMITER $$

CREATE PROCEDURE `SelectChronoRange`(IN timeBegin BIGINT,
    IN timeEnd BIGINT)
BEGIN
    DECLARE totalAvail, skip, insideResultMax INT;
    SET @maxResults = 64;

    SELECT count(*)
    INTO totalAvail
    FROM `dediwatcherstats`;

    SET insideResultMax:= @maxResults - 2;
    SET skip := CEIL(totalAvail / insideResultMax);
    SET @firstpid = 0;
    SET @lastpid = 0;

    SELECT `pid` INTO @firstpid
    FROM `dediwatcherstats`
    WHERE
        CASE
            WHEN timeBegin IS NOT NULL AND timeEnd IS NOT NULL THEN
                `Time`>=timeBegin AND `Time`<=timeEnd
            WHEN timeEnd IS NOT NULL THEN
                `Time`<=timeEnd
            WHEN timeBegin IS NOT NULL THEN
                `Time`>=timeBegin
            ELSE
                TRUE
        END
    ORDER BY `Time` ASC, `pid` ASC LIMIT 1;

    SELECT `pid` INTO @lastpid
    FROM `dediwatcherstats`
    WHERE
        CASE
            WHEN timeBegin IS NOT NULL AND timeEnd IS NOT NULL THEN
                `Time`>=timeBegin AND `Time`<=timeEnd
            WHEN timeEnd IS NOT NULL THEN
                `Time`<=timeEnd
            WHEN timeBegin IS NOT NULL THEN
                `Time`>=timeBegin
            ELSE
                TRUE
        END
    ORDER BY `Time` DESC, `pid` DESC LIMIT 1;

    SELECT * FROM
    (
        (
            SELECT * FROM `dediwatcherstats`
            WHERE `pid`=@firstpid
        )
    UNION
        (
        SELECT * FROM `dediwatcherstats`
        WHERE
            CASE
                WHEN timeBegin IS NOT NULL AND timeEnd IS NOT NULL THEN
                    `Time`>=timeBegin AND `Time`<=timeEnd
                WHEN timeEnd IS NOT NULL THEN
                    `Time`<=timeEnd
                WHEN timeBegin IS NOT NULL THEN
                    `Time`>=timeBegin
                ELSE
                    TRUE
            END
            AND `pid` % skip=0
        LIMIT 62
        )
    ) AS notused
    UNION
        SELECT * FROM `dediwatcherstats`
        WHERE `pid`=@lastpid;
END

CREATE TABLE `dediwatcherstats` (
  `pid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Time` bigint(20) unsigned NOT NULL,
  `Data` text,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

不过，我希望LIMIT子句允许参数变量。在我发布的代码中，我对任何想要使用它的人都使用了64个而不是32个的限制。