выбрать группу по измерению и накопить процент столбца

Я использую ClickHouse, и у меня есть эта таблица

| URL           | visits        |
| ------------- |:------------- |
| URL1          | 5             |
| URL2          | 30            |
| URL3          | 1             |
| URL4          | 30            |
| URL5          | 9             |
| URL1          | 5             |
| URL2          | 20            |

Я могу группировать по URL,

select
    url,
    sum(visits) as visits
from
    database.tableVistis
group by
    url

| URL           | visits        |
| ------------- |:------------- |
| URL1          | 10            |
| URL2          | 50            |
| URL3          | 1             |
| URL4          | 30            |
| URL5          | 9             |

И мне нужен этот результат (группа по URL,% от общего количества посещений и% сумма накапливается)

| URL           | visits        | %        | Accumulate |
| ------------- |:------------- |----------|------------|
| URL2          | 50            |50%       | 50%        |
| URL4          | 30            |30%       | 80%        |
| URL1          | 10            |10%       | 90%        |
| URL5          | 9             |9%        | 99%        |
| URL3          | 1             |1%        | 100%       |

Любая идея? Спасибо!!


clickhouse
person lino    schedule 21.04.2020    source источник
comment
какую базу данных вы используете? пожалуйста, отметьте это.   -  person zealous    schedule 21.04.2020
comment
ClickHouse   -  person lino    schedule 21.04.2020
comment
заголовок вопроса содержит ссылку на runningAccumulate в вашем вопросе нет ссылок на состояние агрегирования. Это сбивает с толку, может нужно исправить заголовок.   -  person vladimir    schedule 21.04.2020
comment
Извините, я очень мало знаю об этом агрегатном состоянии :(   -  person lino    schedule 21.04.2020

Ответы (2)


arrow_upward
3
arrow_downward

Попробуйте этот запрос:

SELECT result.1 AS URL, result.2 AS visits, round(result.3,2) AS "%", round(result.4, 2) AS Accumulate
FROM (
    SELECT 
        groupArray((URL, visits)) url_visits,
        arraySum(x -> x.2, url_visits) total_visits,
        arrayMap(x -> (100 / total_visits) * x.2, url_visits) percent_visits,
        arrayCumSum(percent_visits) acc_percent_visits,
        arrayJoin(arrayMap((x, y, z) -> (x.1, x.2, y, z), url_visits, percent_visits, acc_percent_visits)) result
    FROM (
        SELECT URL, sum(visits) visits
        FROM (
            /* test data */
            SELECT data.1 URL, data.2 visits
            FROM (
                SELECT arrayJoin([
                    ('URL1', 5 ),
                    ('URL2', 30),
                    ('URL3', 1 ),
                    ('URL4', 30),
                    ('URL5', 9 ),
                    ('URL1', 5 ),
                    ('URL2', 20)]) data))
        GROUP BY URL
        ORDER BY visits DESC))
/* result 
┌─URL──┬─visits─┬──%─┬─Accumulate─┐
│ URL2 │     50 │ 50 │         50 │
│ URL4 │     30 │ 30 │         80 │
│ URL1 │     10 │ 10 │         90 │
│ URL5 │      9 │  9 │         99 │
│ URL3 │      1 │  1 │        100 │
└──────┴────────┴────┴────────────┘

*/
person vladimir    schedule 21.04.2020

arrow_upward
0
arrow_downward

Да! Спасибо!!

SELECT
    result.1 AS URL,
    result.2 AS visits,
    round(result.3,
    2) AS "%",
    round(result.4,
    2) AS Accumulate
FROM
    (
    SELECT
        groupArray((URL,
        visits)) url_visits,
        arraySum(x -> x.2,
        url_visits) total_visits,
        arrayMap(x -> (100 / total_visits) * x.2,
        url_visits) percent_visits,
        arrayCumSum(percent_visits) acc_percent_visits,
        arrayJoin(arrayMap((x,
        y,
        z) -> (x.1,
        x.2,
        y,
        z),
        url_visits,
        percent_visits,
        acc_percent_visits)) result
    FROM
        (
        SELECT
            URL,
            sum(visits) visits
        FROM
            (/* test data */
            SELECT
                URL,
                visits
            FROM
                (
                select
                    landing as URL,
                    visitas as visits
                from
                    Analytics
                where
                    fecha > '2019-05-01'))
        GROUP BY
            URL
        ORDER BY
            visits DESC))

Этот запрос более компактный:

SELECT 
    result.1 AS URL, 
    result.2 AS visits, 
    round(result.3, 2) AS `%`, 
    round(result.4, 2) AS Accumulate
FROM 
(
    SELECT 
        groupArray((URL, visits)) AS url_visits, 
        arraySum(x -> (x.2), url_visits) AS total_visits, 
        arrayMap(x -> ((100 / total_visits) * (x.2)), url_visits) AS percent_visits, 
        arrayCumSum(percent_visits) AS acc_percent_visits, 
        arrayJoin(arrayMap((x, y, z) -> (x.1, x.2, y, z), url_visits, percent_visits, acc_percent_visits)) AS result
    FROM 
    (
        SELECT 
            landing AS URL, 
            sum(visitas) AS visits
        FROM Analytics
        WHERE fecha > '2019-05-01'
        GROUP BY URL
        ORDER BY visits DESC
    )
)
person lino    schedule 21.04.2020