mysql - SQL查询最后连续相同的数据

Question

我的表中有数据

+----+------------+------+
| Id | data_date  | data |
+----+------------+------+
|  1 | 01/01/2017 |    1 |
|  1 | 01/02/2017 |    0 |
|  1 | 01/03/2017 |    0 |
|  1 | 01/04/2017 |    0 |
|  2 | 01/02/2017 |    0 |
|  2 | 01/03/2017 |    0 |
|  2 | 01/04/2017 |    1 |
|  2 | 01/05/2017 |    1 |
+----+------------+------+

数据列只有值 0 和 1

如何获取最近几天连续的相同数据类型，即每个 ID 的“0”或“1”

+----+-----------+-----------------+
| Id | data_Type | Last_Data_Count |
+----+-----------+-----------------+
|  1 |         0 |               3 |
|  2 |         1 |               2 |
+----+-----------+-----------------+

Answer 1

Oracle 查询:

SELECT id,
       MAX( last_data ) AS data_type, 
       COUNT(*) AS last_data_count
FROM   (
  SELECT d.*,
         MAX ( CASE WHEN data <> last_data THEN data_date END )
           OVER ( PARTITION BY id ) AS last_date
  FROM   (
    SELECT d.*,
           MAX( data ) KEEP ( DENSE_RANK LAST ORDER BY data_date )
             OVER ( PARTITION BY id ) AS last_data
    FROM   data d
  ) d
)
WHERE  data_date > last_date
GROUP BY id;