#!/usr/bin/env python3
"""
第 8 章 · 典型聚合 / 窗口 / 漏斗 / 留存 / 数组操作样板

跑之前请先：
    clickhouse-client < init.sql
    python3 ../seed.py

依赖：pip install clickhouse-connect
"""
from __future__ import annotations

import time

import clickhouse_connect

HOST = "127.0.0.1"
PORT = 8123


def show(client, title: str, sql: str) -> None:
    print(f"\n=== {title} ===")
    print(sql.strip())
    t0 = time.time()
    res = client.query(sql)
    cost = (time.time() - t0) * 1000
    print(f"--- {cost:.1f} ms, {len(res.result_rows)} rows")
    for row in res.result_rows[:10]:
        print(" ", row)
    if len(res.result_rows) > 10:
        print(f"  ... ({len(res.result_rows) - 10} more)")


def main() -> None:
    client = clickhouse_connect.get_client(host=HOST, port=PORT, username="default")

    show(client, "1. 基础聚合（PV/UV/GMV 一句出）", """
        SELECT
            count()                        AS pv,
            uniq(uid)                      AS uv,
            uniqExact(uid)                 AS uv_exact,
            countIf(event_type='pay')      AS pay_cnt,
            sumIf(amount, event_type='pay') AS gmv
        FROM learn_ck.user_actions
    """)

    show(client, "2. uniq vs uniqExact vs uniqHLL12 误差对比", """
        SELECT
            uniq(uid)        AS approx,
            uniqExact(uid)   AS exact,
            uniqHLL12(uid)   AS hll12,
            (uniq(uid) - uniqExact(uid)) / uniqExact(uid) AS err_uniq,
            (uniqHLL12(uid) - uniqExact(uid)) / uniqExact(uid) AS err_hll
        FROM learn_ck.user_actions
    """)

    show(client, "3. WITH ROLLUP 多维上卷", """
        SELECT country, city, count() AS cnt
        FROM learn_ck.user_actions
        GROUP BY country, city WITH ROLLUP
        ORDER BY country, city
        LIMIT 20
    """)

    show(client, "4. 分位数 + Top-K 一句搞定", """
        SELECT
            quantiles(0.5, 0.9, 0.99)(duration) AS [p50, p90, p99],
            topK(5)(url)  AS top5_pages,
            argMax(url, ts) AS last_page
        FROM learn_ck.user_actions
    """)

    show(client, "5. 窗口函数：每个用户访问的累计时长 + 上一页", """
        SELECT
            uid, ts, url,
            row_number() OVER (PARTITION BY uid ORDER BY ts) AS rn,
            lag(url, 1)  OVER (PARTITION BY uid ORDER BY ts) AS prev,
            sum(duration) OVER (PARTITION BY uid ORDER BY ts
                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                               ) AS cum_dur
        FROM learn_ck.user_actions
        WHERE uid = (SELECT min(uid) FROM learn_ck.user_actions)
        ORDER BY ts
        LIMIT 10
    """)

    show(client, "6. LIMIT N BY：每个 uid 取最近 3 条，比窗口函数快", """
        SELECT uid, ts, url
        FROM learn_ck.user_actions
        ORDER BY uid, ts DESC
        LIMIT 3 BY uid
        LIMIT 12
    """)

    show(client, "7. ARRAY JOIN：把 tags 数组展开成行", """
        SELECT tag, count() AS cnt
        FROM learn_ck.user_actions
        ARRAY JOIN tags AS tag
        GROUP BY tag
        ORDER BY cnt DESC
        LIMIT 10
    """)

    show(client, "8. 高阶数组：Lambda 算用户活跃度", """
        SELECT
            uid,
            length(groupArray(url))                                  AS pv,
            arrayUniq(groupArray(url))                               AS distinct_url,
            arrayCount(x -> x > 60, groupArray(duration))            AS heavy_steps,
            arraySum(x -> x, groupArray(duration))                   AS total_dur
        FROM learn_ck.user_actions
        GROUP BY uid
        ORDER BY pv DESC
        LIMIT 5
    """)

    show(client, "9. windowFunnel：4 步漏斗", """
        SELECT level, count() AS users
        FROM (
            SELECT
                uid,
                windowFunnel(1800)(
                    ts,
                    event_type='view',
                    event_type='click',
                    event_type='addcart',
                    event_type='pay'
                ) AS level
            FROM learn_ck.user_actions
            GROUP BY uid
        )
        GROUP BY level
        ORDER BY level
    """)

    show(client, "10. retention：D0 / D1 / D7 留存", """
        SELECT
            sum(ret[1]) AS d0,
            sum(ret[2]) AS d1,
            sum(ret[3]) AS d7,
            round(sum(ret[2]) / sum(ret[1]), 4) AS d1_rate,
            round(sum(ret[3]) / sum(ret[1]), 4) AS d7_rate
        FROM (
            SELECT
                uid,
                retention(
                    toDate(ts) = today() - 7,
                    toDate(ts) = today() - 6,
                    toDate(ts) = today()
                ) AS ret
            FROM learn_ck.user_actions
            GROUP BY uid
        )
    """)

    show(client, "11. WITH FILL：把空日期补齐", """
        SELECT toDate(ts) AS day, count() AS pv
        FROM learn_ck.user_actions
        WHERE day BETWEEN today() - 14 AND today()
        GROUP BY day
        ORDER BY day WITH FILL FROM today() - 14 TO today() + 1 STEP 1
    """)

    show(client, "12. -State / -Merge：从聚合表取最终值", """
        SELECT
            day,
            countMerge(pv)               AS pv,
            uniqMerge(uv)                AS uv,
            sumMerge(gmv)                AS gmv,
            quantileMerge(0.99)(p99_dur) AS p99
        FROM learn_ck.user_actions_daily
        GROUP BY day
        ORDER BY day
        LIMIT 7
    """)


if __name__ == "__main__":
    main()