42403


google big queryでのSQLで、  
グループ別の合計に対する比率を出す方法に悩んでおります。  
(通常のsqlの回答でもうれしいです)  
用意しているテーブルは以下です。

[code]

    score_data
    +--------+-------+------+
    | userID | score |  age |
    +--------+-------+------+
    |    1   |   100 |   5  |
    |    2   |   113 |   14 |
    |    3   |   250 |   19 |
    |    4   |   300 |   35 |
    |    5   |   450 |   42 |
    |   ...  |   ... |  ... |
    +--------+-------+------+
    
    age_group(例: age:0～20の人はage_group_id が1)
    +--------------+-------+------+
    | age_group_id | min   | max  |
    +--------------+-------+------+
    |       1      |     0 |   20 |
    |       2      |    21 |   40 |
    |       3      |    41 |   60 |
    +--------------+-------+------+
    
    score_range(例: score:0～100の人はrange_idが1)
    +----------+----------------+------+------+
    | range_id |      name      | min  |  max |
    +----------+----------------+------+------+
    |     1    |    bad luck    |    0 |  100 |
    |     2    | uncertain luck |  101 |  200 |
    |     3    |    fair luck   |  201 |  300 |
    |     4    |    good luck   |  301 |  400 |
    |     5    |    best luck   |  401 |  500 |
    +----------+----------------+------+------+
    
[/code]

欲しいテーブルは以下のような要素のテーブルです。

[code]

    dist_data
    +----------------+-------------+---------------+--------------+
    |      name      |  user_count |  age_group_id |  ratio_in_ag |
    +----------------+-------------+---------------+--------------+
    |    bad luck    |          10 |             1 |           10 |
    |    bad luck    |           0 |             2 |            0 |
    |    bad luck    |          20 |             3 |           20 |
    | uncertain luck |          20 |             1 |           20 |
    | uncertain luck |           0 |             2 |            0 |
    | uncertain luck |          10 |             3 |           10 |
    |    fair luck   |          30 |             1 |           30 |
    |    fair luck   |          70 |             2 |           70 |
    |    fair luck   |          20 |             3 |           20 |
    |    good luck   |          30 |             1 |           30 |
    |    good luck   |          30 |             2 |           30 |
    |    good luck   |          40 |             3 |           40 |
    |    best luck   |          10 |             1 |           10 |
    |    best luck   |           0 |             2 |            0 |
    |    best luck   |          10 |             3 |           10 |
    +----------------+-------------+---------------+--------------+
    
[/code]

nameはscoreの範囲の名前、  
age_group_idは、年齢の区分にあたるID、  
user_countは、その年齢区分でnameのスコア範囲に所属するユーザ数  
ratio_in_agは、同じage_group_idの全てのスコア範囲の合計人数に対するuser_countの割合  
です。

（例: age_group_id:1のユーザが10人、そのうちname:bad luckのユーザが1人だとすると以下のような行になります）

[code]

    +----------------+-------------+---------------+--------------+
    |      name      |  user_count |  age_group_id |  ratio_in_ag |
    +----------------+-------------+---------------+--------------+
    |    bad luck    |           1 |             1 |           10 |
    +----------------+-------------+---------------+--------------+
    
[/code]

現在ratio_in_ag以外の値は以下のSQLで取得しています。

[code]

    #standardSQL
    SELECT 
        x.name, count(user_id) as user_count, age_group_id
    FROM
        (
            SELECT 
                range_name, user_id, age_group_id
            FROM 
                dataset.score_data
            JOIN 
                dataset.age_group
            ON 
                score_data.age BETWEEN age_group.min AND age_group.max
            JOIN 
                dataset.score_range 
            ON score_data.score BETWEEN score_range.min AND score_range.max
        ) as c 
    RIGHT JOIN 
        dataset.score_range as x 
    ON 
        c.name = x.name
    GROUP BY 
        name, age_group_id
    
[/code]

GROUP BYしたage_group_id毎のuser_countが分かれば可能だと思うのですが、  
どう求めればよいのか、お分かりになる方ご教授お願いいたします。


CC BY-SA 3.0
2018-03-14T02:22:55.280
42381
2018-03-15T01:31:03.010
24104
question
sql google-bigquery
グループ別の合計に対する比率を出したい


SQL一般の話として回答します。

[code]

    SELECT
        sr.name AS name
    ,   COUNT(*) AS user_count
    ,   ag.age_group_id AS age_group_id
    ,   (100 * COUNT(*) / sba.count_in_age_group) AS ratio_in_ag
    FROM
        score_data AS sd
        INNER JOIN age_group AS ag
            ON sd.age BETWEEN ag.min AND ag.max
        INNER JOIN score_range AS sr
            ON sd.score BETWEEN sr.min AND sr.max
        INNER JOIN
        (
            SELECT
                ag0.age_group_id
            ,   COUNT(*) AS count_in_age_group
            FROM
                score_data AS sd0
                INNER JOIN age_group AS ag0
                    ON sd0.age BETWEEN ag0.min AND ag0.max
            GROUP BY
                 ag0.age_group_id
        ) AS sba
            ON ag.age_group_id = sba.age_group_id
    GROUP BY
        sr.range_id, sr.name, ag.age_group_id
    ;   
    
[/code]

BigQuery での動作は確認していませんので参考までにどうぞ。


CC BY-SA 3.0
2018-03-15T01:31:03.010
42403
2018-03-15T01:31:03.010
27410
42381
answer