从第一和最后一个记录匹配的标准

问题描述：

选择列考虑以下表中，其与资产的调度涉及的应用程序：从第一和最后一个记录匹配的标准

date  group_id free_spots 
2011-01-01 1   0 
2011-01-01 2   0 
2011-01-08 1   1 
2011-01-08 2   0 
2011-01-15 1   1 
2011-01-15 2   1 
2011-01-22 1   2 
2011-01-22 2   2 
2011-01-29 1   1 
2011-01-29 2   0 
2011-02-05 1   0 
2011-02-05 2   1 
2011-02-12 1   0 
2011-02-12 2   1 
2011-02-19 1   0 
2011-02-19 2   0

此信息放在一起使用利用不同的表一个相当昂贵的查询（〜100ms的）。结果可以放在临时表中，也可以直接内联使用。

我想要的是找到提供点的第一个日期（free_spots> 0）。然后在同一张唱片中，我想要最后一个拍摄点的日期。所有这些都由group_id分组。

为了说明，在给定的示例表，我希望下面的输出：

group_id start_date end_date 
1   2011-01-08 2011-01-29 
2   2011-01-15 2011-01-22 
2   2011-02-05 2011-02-12

现在，我已经想出了一个粗略的解决方案。使用给定的表我想：

抓住这些之前*由NULL或与free_spots记录< = 0（开始日期）
对于所有这些记录的所有记录，抢到第一接班人*是成功由一行为空或有free_spots < = 0
以某种方式在这里混合group_id分组。

但是，这似乎是不可能的，因为我不能再次使用相同的子查询来查找后继或前面的记录。与临时表格相同。这些我无法打开并重复使用一次以上。

（*在之前或基于该日期成功了。对于每个组，该日期是相等的，连续的和均匀的（但任意地）间隔开。一般7或14天）

请参阅我的更新，有一个MySQL的翻译为我工作的SQL Server的答案的开始。 – ErikE 2012-07-24 00:32:16

答

它可能不是超高效的，但它适用于您的数据。（请注意，我添加了一个WHERE约束的日期范围，如果你想要一个）：

SELECT group_id,MIN(`date`) AS start_date, 
     (SELECT `date` FROM Slots s3 
     WHERE s3.group_id=t.group_id 
     AND s3.`date`<t.next_stop_date 
     AND s3.free_spots > 0 
     ORDER BY s3.`date`DESC 
     LIMIT 1) as end_date 
FROM 
    (SELECT s1.*, MIN(s2.`date`) AS next_stop_date 
    FROM 
    Slots s1 LEFT JOIN Slots s2 
     ON s2.`date` > s1.date AND s1.group_id=s2.group_ID AND s2.free_spots = 0 
    WHERE s1.free_spots > 0 
    GROUP BY s1.group_id, s1.`date` 
    ORDER BY s1.group_id ASC, s1.`date` ASC 
) AS t 
GROUP BY group_id, next_stop_date

不幸的是，这似乎给我与我的尝试遇到同样的错误：无法重新打开表's3'。另外，这些日期在查询中被硬编码的原因是什么？我想我可以忽略这些以处理整个表格？ – ChrisDekker 2012-07-24 00:07:36

我不确定你的意思是“无法重新打开表格s3'”。您可以删除日期而不影响任何内容。（我只是编辑了答案，以便他们不再在那里） – 2012-07-24 00:13:59

您可以查看数据并使用http://sqlfiddle.com/#!2/0f4b5/12/0 – 2012-07-24 00:24:24

答

予想不到的超级简单的方法来做到这一点。这里有一个方法的草图，我可以（使用多个查询）

create temporary table temp1 select group_id,min(date) as start_date from table1 where free_spots>0 group by group_id

alter table temp1 add column end_date datetime default null

create temporary table TEMP2在while循环select * from table1 where free_spots>0

然后（使用某种编程语言），想起来了，我会做下列事情，直到temp2为空。你应该在回路中的每一个步骤（称之为$ CURDATE）增加日期到第二天：

update temp2,temp1 set temp1.end_date=temp2.date where temp1.group_id=temp2.group_id and temp2.date='$curDate' and temp2.free_spots>0

你可以，如果有0行更新，每次查询后检查。如果他们是，你就完成了，你可以打破while循环。

这让我觉得解决方案更好地放在应用程序端（Rails 3.x）而不是MySQL。 – ChrisDekker 2012-07-24 00:09:35

此外，只选择free_spots> 0到第二个临时表中的记录将合并该组的所有开始/结束范围。例如，自由点批次[1,2,2,1,0,0,1,2,1]将作为[1,2,2,1,1,2,1]插入，从而丢失来自中心...或者我一定完全误会了你...... – ChrisDekker 2012-07-24 00:19:59

答

这在SQL服务器上工作（如果我正确理解你的问题）。它应在MySQL工作，以及：

选择a.group_id，a.min_date，从b.max_date（选择s1.group_id，分钟（s1.date）MIN_DATE 从点S1 组由s1.group_id） a 内部连接
（从group_id中选择group_id，max（date）max_date group_id）a.group_id = b上的。group_id

这会给每组最多1个开始/结束组合。考虑一批空闲点[1,2,2,1,0,0,1,2,1]。对于同一组，显然应该是[1,2,2,1]和[1,2,1]。 2条记录。 – ChrisDekker 2012-07-24 00:13:44

答

我可以在SQL Server写这篇文章，并知道这是翻译到MySQL。首先我会给你SQL Server版本，然后在下面的翻译给你一个提升。我会跳过这个问题，但最初并没有意识到这是针对MySQL的。

这容忍日期之间的任何长度的可变间隙。

WITH IDs AS (
    SELECT *, Row_Number() OVER (PARTITION BY GroupID ORDER BY AvailableDate) ID 
    FROM Availability 
), Data AS (
    SELECT 
     GroupID, 
     AvailableDate, 
     ID - Dense_Rank() OVER (PARTITION BY GroupID ORDER BY ID) G 
    FROM IDs 
    WHERE FreeSpots > 0 
) 
SELECT 
    GroupID, 
    Min(AvailableDate) FromDate, 
    Max(AvailableDate) ToDate 
FROM Data 
GROUP BY GroupID, G;

这里的安装脚本：

CREATE TABLE Availability (
    AvailableDate datetime, 
    GroupID tinyint, 
    FreeSpots tinyint 
) 
INSERT Availability 
SELECT '20110101', 1, 0 
UNION ALL SELECT '20110101', 2, 0 
UNION ALL SELECT '20110108', 1, 1 
UNION ALL SELECT '20110108', 2, 0 
UNION ALL SELECT '20110115', 1, 1 
UNION ALL SELECT '20110115', 2, 1 
UNION ALL SELECT '20110122', 1, 2 
UNION ALL SELECT '20110122', 2, 2 
UNION ALL SELECT '20110129', 1, 1 
UNION ALL SELECT '20110129', 2, 0 
UNION ALL SELECT '20110205', 1, 0 
UNION ALL SELECT '20110205', 2, 1 
UNION ALL SELECT '20110212', 1, 0 
UNION ALL SELECT '20110212', 2, 1 
UNION ALL SELECT '20110219', 1, 0 
UNION ALL SELECT '20110219', 2, 0

MySQL的翻译

下应相当于我的第一CTE（公共表表达式），模拟ROW_NUMBER（）函数。稍微调整一下，你可以用这个作为派生表来做第二个CTE来模拟Dense_Rank（），并且你有一个工作查询！

SELECT 
    GroupID, 
    AvailableDate, 
    FreeSpots, 
    @rownum=CASE WHEN @grpset <> GroupID THEN 0 ELSE @rownum + 1 END AS rownum, 
    @grpset=GroupID AS grpset 
FROM 
    (SELECT @grpset= -1) g, 
    (SELECT @rownum:= -1) r, 
    (SELECT * 
    FROM Availability 
    ORDER BY GroupID, AvailableDate 
    ) a

我知道MySQL不是一个小小的东西，所以从网上的一个例子来说，这应该工作，但我可以让语法错误在那里滑。如果此查询有效，并且您需要更多帮助，请告诉我，我将尝试将其应用到MySQL的完整查询中。虽然如果@Quassnoi出现，你很快就会拥有它！

感谢您的输入。很抱歉，我没有更清楚使用MySQL。我很感谢你的回答，但我将与Holger Brandt的解决方案一起进行，因为它直接对数据进行操作，对我来说更符合逻辑。如果我遇到麻烦，我会尝试你的解决方案。 – ChrisDekker 2012-07-24 00:40:36

你很清楚，但它被编辑出标题。我绝不会建议任何人使用MySQL，因为你刚才指出的原因......非常伤心。 – ErikE 2012-07-24 00:44:38

答

查询是不是很好，但似乎工作：

SELECT * 
FROM (

SELECT a.group_id, a.`date` AS 
start_date , max(b.`date`) AS 
end_date 
FROM test AS a 
LEFT JOIN test AS b ON a.group_id = b.group_id 
AND b.free_spots >0 
AND a.date < b.date 
WHERE a.free_spots >0 
AND (

SELECT count(*) 
FROM test AS c 
WHERE c.group_id = a.group_id 
AND c.date > a.date 
AND c.date < b.date 
AND free_spots =0 
) =0 
GROUP BY group_id, 
start_date 
) AS d 
WHERE end_date IS NOT NULL 
GROUP BY d.end_date 
ORDER BY `d`.`group_id` ASC

从第一和最后一个记录匹配的标准

相关推荐