交付出件箱

← 返回出件箱

驾驶舱大屏 · 数据摸底 SQL（执行说明）

2026-08-14 · 配套《渠道机构地理分布驾驶舱大屏调研报告》
执行人：具备测试库 / 生产库只读权限的同事
耗时：全部 4 条约 1 分钟
风险：全部是 SELECT 只读查询，不改任何数据

为什么要先跑这个

大屏的整体排期，就取决于这几条查询的结果。核心问题有三个：

1. 机构有没有地址？（决定地图能亮几个点，也决定"各省销售排名"能不能做）
2. 登录记录里的 IP 是不是真的？（决定"登录点亮地区"能不能成立）
3. 三个口径的销售额实际差多少？（决定大屏主口径用哪个 · v3 新增）

在这三个答案出来之前，任何排期都是猜的。

v3 更新（2026-08-15）：新增第【5】【6】两段，摸底经营数据（销售额三口径差异、
订单状态分布、时间跨度与账期覆盖）。这两段直接支撑报告 v3.0 的"老板视角指标体系"，
其中口径差异是必须由业务拍板的第一优先级决策。

一次性执行脚本

连上库后整段贴进去即可，四段会依次输出四张结果表。

-- ============================================================
-- 【1】机构地址覆盖率 · 两个候选来源分别有多少
-- ============================================================
SELECT
    COUNT(*)                                                          AS 启用机构总数,
    COUNT(*) FILTER (WHERE o.store_address IS NOT NULL
                       AND o.store_address <> '')                     AS 有门店名片地址,
    COUNT(*) FILTER (WHERE o.latitude IS NOT NULL
                       AND o.longitude IS NOT NULL)                   AS 有经纬度坐标,
    COUNT(*) FILTER (WHERE p.address IS NOT NULL
                       AND p.address <> '')                           AS 有签约地址,
    COUNT(*) FILTER (WHERE COALESCE(NULLIF(p.address, ''),
                                    NULLIF(o.store_address, '')) IS NOT NULL)
                                                                      AS 两源合并可定位,
    ROUND(100.0 * COUNT(*) FILTER (WHERE COALESCE(NULLIF(p.address, ''),
                                    NULLIF(o.store_address, '')) IS NOT NULL)
          / NULLIF(COUNT(*), 0), 1)                                   AS 可定位百分比
FROM organizations o
LEFT JOIN org_signup_profiles p ON p.org_id = o.id
WHERE o.is_active = true;


-- ============================================================
-- 【2】登录 IP 真实性体检 · 最重要的一条
-- 若"私网IP占比"很高 → 说明记的是机房内部地址，不是用户真实位置
-- ============================================================
SELECT
    COUNT(*)                                                          AS 近30天登录次数,
    COUNT(DISTINCT ip)                                                AS 不同IP数,
    COUNT(*) FILTER (WHERE ip IS NULL)                                AS IP为空的记录,
    COUNT(*) FILTER (WHERE ip << '10.0.0.0/8'
                        OR ip << '172.16.0.0/12'
                        OR ip << '192.168.0.0/16')                    AS 私网IP记录数,
    ROUND(100.0 * COUNT(*) FILTER (WHERE ip << '10.0.0.0/8'
                        OR ip << '172.16.0.0/12'
                        OR ip << '192.168.0.0/16')
          / NULLIF(COUNT(*), 0), 1)                                   AS 私网IP占比
FROM auth_audit_logs
WHERE event_type = 'login_success'
  AND created_at > now() - interval '30 days';


-- ============================================================
-- 【3】谁在登录 · 决定下钻面板给谁看、看什么
-- ============================================================
SELECT
    u.role                                                            AS 账号类型,
    COUNT(*)                                                          AS 登录次数,
    COUNT(DISTINCT u.id)                                              AS 涉及账号数,
    COUNT(DISTINCT u.linked_org_id)                                   AS 涉及机构数
FROM auth_audit_logs a
JOIN users u ON u.id = a.user_id
WHERE a.event_type = 'login_success'
  AND a.created_at > now() - interval '30 days'
GROUP BY u.role
ORDER BY 登录次数 DESC;


-- ============================================================
-- 【5】销售额三口径实际差异 · 决定大屏主口径用哪个(v3 新增)
-- 三个数在任何时点都不会相等,先看清差多少再定口径
-- ============================================================
SELECT
    (SELECT COALESCE(SUM(amount_due), 0) FROM store_service_orders
      WHERE status = '已完成')                                      AS 订单应收_已完成,
    (SELECT COALESCE(SUM(amount_due), 0) FROM store_service_orders
      WHERE status NOT IN ('已完成','已取消','已驳回'))              AS 订单应收_在途,
    (SELECT COALESCE(SUM(confirmed_amount), 0)
       FROM payment_confirmations)                                  AS 确认到账_累计,
    (SELECT COALESCE(SUM(received_amount), 0)
       FROM revenue_snapshots)                                      AS 收益快照_累计,
    (SELECT COALESCE(SUM(channel_amount), 0)
       FROM revenue_snapshots)                                      AS 其中渠道分成;

-- 订单状态分布(看有多少卡在中间态 · 入驻漏斗的数据基础)
SELECT status AS 订单状态, order_type AS 订单类型,
       COUNT(*) AS 单数,
       COALESCE(SUM(amount_due), 0) AS 金额合计,
       COUNT(*) FILTER (WHERE amount_due IS NULL) AS 金额待确认单数
FROM store_service_orders
GROUP BY status, order_type
ORDER BY 单数 DESC;


-- ============================================================
-- 【6】经营数据时间跨度 · 决定趋势图和同比环比能回溯多久(v3 新增)
-- ============================================================
SELECT
    MIN(confirmed_at)                                               AS 最早到账时间,
    MAX(confirmed_at)                                               AS 最近到账时间,
    COUNT(*)                                                        AS 到账笔数,
    COUNT(DISTINCT org_id)                                          AS 涉及机构数
FROM payment_confirmations;

-- 近 12 个月按月到账(趋势图与同比的可行性 · 顺带看业务节奏)
SELECT to_char(confirmed_at AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM') AS 月份,
       COUNT(*) AS 笔数,
       SUM(confirmed_amount) AS 到账金额
FROM payment_confirmations
WHERE confirmed_at > now() - interval '12 months'
GROUP BY 1 ORDER BY 1 DESC;

-- 账期覆盖(半月账期制 · 确认历史账期键格式是否混用)
SELECT period_key AS 账期, COUNT(*) AS 快照数, SUM(received_amount) AS 实收合计
FROM revenue_snapshots
GROUP BY period_key ORDER BY period_key DESC LIMIT 12;


-- ============================================================
-- 【4】渠道维度分布 · 渠道卡片的数据基础 + 顺带看登录历史留存多久
-- ============================================================
SELECT
    c.name                                                            AS 渠道名称,
    c.status                                                          AS 渠道状态,
    COUNT(p.org_id)                                                   AS 名下机构数,
    COUNT(*) FILTER (WHERE p.status = '已开通')                        AS 已开通机构数
FROM channels c
LEFT JOIN org_signup_profiles p ON p.channel_id = c.id
GROUP BY c.name, c.status
ORDER BY 名下机构数 DESC;

SELECT
    MIN(created_at)                                                   AS 最早一条登录记录,
    MAX(created_at)                                                   AS 最新一条登录记录,
    COUNT(*)                                                          AS 登录记录总条数
FROM auth_audit_logs;

结果怎么读

看第【1】条的"可定位百分比"

看第【2】条的"私网IP占比"

另外看"IP为空的记录"——如果占比明显，说明有一部分登录没记到 IP，需要单独排查。

看第【4】条的"最早一条登录记录"

如果最早记录能追溯到系统上线时，说明历史登录数据一条没删，大屏可以做长周期回看和历史趋势。如果只有最近半年，那历史视图就只能做到半年。

附：IP 归属地能力已在本机实测通过

不用等摸底结果，"把 IP 翻译成省市"这个能力已经验证可行：

• 精度：省 + 市 + 运营商，满足大屏需求
• 速度：8 次查询 1.55 毫秒，实时上屏无压力
• 不出网：本地库文件查询，登录 IP 不会发给任何第三方
• ⚠️ 已知短板：现成库文件是 2022 年版本，近三年新分配的 IP 段可能识别不准。需要安排有外网权限的同事下载一份最新库文件（本开发机外网受限，拿不到新版）。这不影响开工，属于上线前要补的一步。

顺带一提：这个库能认出"内网IP"，所以上线后可以拿它当健康探针——一旦大批登录被解析成内网 IP，就说明部署配置被改坏了，能第一时间发现。

本文件所有 SQL 均为只读查询。执行结果请回填后同步，用于确定大屏正式排期。

【补充】续费与到期摸底（2026-08-15 新增 · 对应调研报告第九章第2/3/4/6/8项）

前 6 段覆盖地址、IP、渠道、金额；本段补齐续费管控所需的 5 项。

-- ============================================================
-- 【7】席位到期分布 · 决定预警列表负载与到期分布视图的价值
-- ============================================================
-- 7a 近期到期规模（三级梯队各多少）
SELECT
    COUNT(*) FILTER (WHERE seat_expires_at <= now() + interval '7 days'
                       AND seat_expires_at > now())                  AS 七天内到期席位,
    COUNT(*) FILTER (WHERE seat_expires_at > now() + interval '7 days'
                       AND seat_expires_at <= now() + interval '30 days')  AS 八到三十天,
    COUNT(*) FILTER (WHERE seat_expires_at > now() + interval '30 days'
                       AND seat_expires_at <= now() + interval '90 days')  AS 三一到九十天,
    COUNT(*) FILTER (WHERE seat_expires_at <= now())                 AS 已过期未续,
    COUNT(DISTINCT linked_org_id) FILTER (
        WHERE seat_expires_at > now() AND seat_expires_at <= now() + interval '90 days'
    )                                                                AS 九十天内涉及机构数
FROM users
WHERE role = 'org_seat' AND is_active = true AND seat_expires_at IS NOT NULL;

-- 7b 未来 12 个月按月到期分布（看有没有续费高峰）
SELECT to_char(seat_expires_at AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM') AS 到期月份,
       COUNT(*) AS 到期席位数,
       COUNT(DISTINCT linked_org_id) AS 涉及机构数
FROM users
WHERE role = 'org_seat' AND is_active = true
  AND seat_expires_at > now()
  AND seat_expires_at <= now() + interval '12 months'
GROUP BY 1 ORDER BY 1;


-- ============================================================
-- 【8】未激活 / 永久席位 · 决定续费分母怎么排除
-- ============================================================
SELECT
    COUNT(*)                                                          AS 席位总数,
    COUNT(*) FILTER (WHERE seat_expires_at IS NOT NULL)               AS 已激活有到期日,
    COUNT(*) FILTER (WHERE seat_expires_at IS NULL
                       AND seat_valid_days IS NOT NULL)               AS 已售未启用,
    COUNT(*) FILTER (WHERE seat_expires_at IS NULL
                       AND seat_valid_days IS NULL)                   AS 永久席位
FROM users
WHERE role = 'org_seat' AND is_active = true;


-- ============================================================
-- 【9】续费与流失历史 · 决定月度曲线和甘特图能回溯多久
-- ============================================================
-- 9a 按月统计续费成功席位数
SELECT to_char(created_at AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM') AS 月份,
       COUNT(*) FILTER (WHERE action = '创建' AND result = '成功') AS 新开席位,
       COUNT(*) FILTER (WHERE action = '续费' AND result = '成功') AS 续费席位,
       COUNT(*) FILTER (WHERE result = '失败')                     AS 执行失败
FROM store_service_order_seats
WHERE created_at > now() - interval '24 months'
GROUP BY 1 ORDER BY 1 DESC;

-- 9b 数据起点（甘特图时间轴能画多长）
SELECT MIN(service_start_at) AS 最早服务起始,
       MAX(service_end_at)   AS 最远服务到期,
       COUNT(*)              AS 席位周期记录数,
       COUNT(DISTINCT core_user_id) AS 涉及席位数
FROM store_service_order_seats;


-- ============================================================
-- 【10】待审批积压 · 决定审批列表参数
-- ============================================================
SELECT
    COUNT(*)                                                          AS 待审核总数,
    COUNT(*) FILTER (WHERE submitted_at < now() - interval '24 hours') AS 超24小时未审,
    MIN(submitted_at)                                                 AS 最久一笔提交时间
FROM org_signup_profiles
WHERE status = '资料待审核';

-- 各状态分布（入驻漏斗基础）
SELECT status AS 签约状态, COUNT(*) AS 机构数
FROM org_signup_profiles
GROUP BY status ORDER BY 机构数 DESC;

这 5 项怎么读