32775


以下のようなID毎の開始地点と終了地点のデータがあった場合、  
各地点毎での有効なID数をSQLで集計する方法を考えているのですが  
いまいちいい方法が思いつきませんどなたかよい方法を教えていただけないでしょうか？

ID毎の開始地点と終了地点のデータ  
ID,start,end  
1,0,3  
2,1,3  
3,2,3  
4,0,1  
5,1,2

地点毎でのID集計  
0地点:2人(1,4)  
1地点:4人(1,2,4,5)  
2地点:4人(1,2,3,5)  
3地点:3人(1,2,3)

※前提としては開始地点と終了地点は時間軸等の連続性あるものとします。


CC BY-SA 3.0
2017-02-18T11:02:16.037
32770
2017-02-18T12:30:53.310
20706
question
sql
ID毎の開始地点と終了地点のデータから各地点毎でIDをカウントする方法


MySQL でクロスジョインを使ってみました。

[code]

    # 適当なテーブルを作成
    CREATE TABLE id_tbl (ID int, start int, end int);
    # insert data
    SELECT * FROM id_tbl;
    +------+-------+------+
    | ID   | start | end  |
    +------+-------+------+
    |    1 |     0 |    3 |
    |    2 |     1 |    3 |
    |    3 |     2 |    3 |
    |    4 |     0 |    1 |
    |    5 |     1 |    2 |
    +------+-------+------+
    
    # query
    SELECT C AS POINT, COUNT(C) AS COUNT, GROUP_CONCAT(ID) AS PERSONS FROM (
      SELECT DISTINCT(C) FROM (
        SELECT start AS C FROM id_tbl UNION ALL SELECT end AS C FROM id_tbl
      ) AS _ ORDER BY C
    ) AS _ CROSS JOIN id_tbl AS B ON C >= B.start and C <= B.end GROUP BY C;
    =>
    +-------+-------+---------+
    | POINT | COUNT | PERSONS |
    +-------+-------+---------+
    |     0 |     2 | 1,4     |
    |     1 |     4 | 1,2,4,5 |
    |     2 |     4 | 1,2,3,5 |
    |     3 |     3 | 1,2,3   |
    +-------+-------+---------+
    
[/code]


CC BY-SA 3.0
2017-02-18T12:24:15.860
32775
2017-02-18T12:30:53.310
2017-02-18T12:30:53.310
32770
answer