Question

我每分钟审议一次表格,其中注明日期、价值两倍。

我试图利用我的智慧确定价值和价值超过3小时的“偶然”。

在我使用询问时:

select date from table where value > 10;

然后,我对持续的日期进行人工阅读。

“发明”实例:

Date             - value
2000/01/01 00:00 - 5
2000/01/01 01:00 - 5
2000/01/01 02:00 - 5
2000/01/01 03:00 - 11
2000/01/01 04:00 - 11
2000/01/01 05:00 - 11
2000/01/01 06:00 - 5
2000/01/01 07:00 - 5
2000/01/01 08:00 - 5
2000/01/01 09:00 - 11
2000/01/01 10:00 - 11
2000/01/01 11:00 - 5

在这种情况下,有1个“发明”在3时至5时之间。

Answer 1

在MySQL中,在检索数据时,你可以将变量在选任书上分配。这一功能有助于解决“通常”使用窗口功能的许多问题(MySQL确实有这种功能)。它也能够帮助你们。我在此最后提出以下解决办法:

SET @startdate = CAST(NULL AS datetime);
SET @granularity = 60;   /* minutes */
SET @minduration = 180;  /* minutes */
SET @minvalue = 10;

SELECT
  t.Date,
  t.Value
FROM (
  SELECT
    StartDate,
    MAX(Date) AS EndDate
  FROM (
    SELECT
      Date,
      Value,
      CASE
        WHEN Value > @minvalue OR @startdate IS NOT NULL
        THEN IFNULL(@startdate, Date)
      END AS StartDate,
      @startdate := CASE
        WHEN Value > @minvalue
        THEN IFNULL(@startdate, Date)
      END AS s
    FROM (
      SELECT Date, Value FROM YourTable
      UNION ALL
      SELECT MAX(Date) + INTERVAL @granularity MINUTE, @minvalue FROM YourTable
    ) s
    ORDER BY Date
  ) s
  WHERE StartDate IS NOT NULL
  GROUP BY StartDate
) s
  INNER JOIN YourTable t ON t.Date >= s.StartDate AND t.Date < s.EndDate
WHERE s.EndDate >= s.StartDate + INTERVAL @minduration MINUTE
;

此处使用的4个变量中,有3个只是文字论据,只有1个是@startdate,实际上在盘问中被分配和核对。

基本上,电离层中层的裂痕,标明值大于特定最低值的电流(@min Value ),最终形成一个时间范围清单,其中数值与情况相符。实际上,为了正确计算终点界限,各组别中还包括紧跟对等的组别的非配对行。因此,在原始数据集中增加了一行,在原始数据集中,<代码>Date的计算方法是在最新<代码>上<>Date加上特定的<代码>@granularity上在表格和上计算出。数值仅为@min Value。



Once obtained, the list of ranges is joined back to the original table to retrieve the detail rows that fall in between the ranges  bounds, the ranges that are not long enough (as specified by @minduration) being filtered out along the way.

如果你执行这一解决办法,请见以下产出:http://sqlfiddle.com/#!2/250e6/5“rel=“nofollow”)。

DATE                            VALUE
------------------------------  -----
January, 01 2000 03:00:00-0800  11
January, 01 2000 04:00:00-0800  11
January, 01 2000 05:00:00-0800  11


which, I understand, is what you would expect.

Answer 2

select count(*) from table where DATE_SUB(CURDATE(),INTERVAL 3 HOUR) < `date`

select count(*) from table where DATE_SUB(CURDATE(),INTERVAL 3 HOUR) < `date` AND `value` > 10

然后对结果进行比较(如果不是相同),则不是连续的。

Answer 3

Wild:

select * from 
    (select event, MAX(date) as date from table where value > 10 group by event) maxs
inner join 
    (select event, MIN(date) as date from table where value > 10 group by event) mins
on maxs.event = mins.event
where (time_to_sec(timediff(maxes.date, mins.date)) / 3600) > 3

友情链接