发生在个人事件发生后的日期

问题描述

我有一组长格式的数据（每人几行，个人= id），事件（事件= 1）应该只发生一次。当事件发生时，不应该有更多的数据。

I have a set of data in long format (several rows per person, person = id) where an event (event = 1) should only occur once. When the event has occurred, there should be no more data from that person.

我想创建一个名为flag（flag = 1）的新变量的查询（如果有）记录在事件发生后出现。

I want to create a query with a new variable called flag (flag = 1) if any record appears after the event has taken place.

例如，id 5被标记在下面，因为在该人的事件之后出现一行数据。所有其他情况都可以。

For example, id 5 is flagged below because a row of data appears after the event for that person. All other scenarios are OK.

所以我需要将ids组合在一起，然后检查活动日期之后发生的任何日期。

So I need to group the ids together, and then check for any date occurring after the event date.

在SQL中实现此目的最有效的方法是什么？

What is the most efficient way of achieving this in SQL?

示例DATA（逗号分隔）：

EXAMPLE DATA (comma separated):

id,date,event,flag
1,01-Aug-14,0,0
1,02-Aug-14,0,0
2,01-Aug-14,0,0
2,02-Aug-14,1,0
3,01-Aug-14,1,0
4,01-Aug-14,0,0
5,01-Aug-14,0,0
5,02-Aug-14,1,0
5,03-Aug-14,0,1

推荐答案

我使用了subselect计算日期之前的ID的事件数。

I used a subselect to count the number of events for an id prior to the date.

SQL Fiddle Demo

select id,
 case 
    when (select count(1) from yourTable t2  where t2.date < t1.date and t1.id = t2.id and t2.event = 1) > 0 then 1
    else 0 
  end as flag
from
yourTable t1

这篇关于发生在个人事件发生后的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！