数仓里常用的 SQL：代表版本、窗口聚合与入仓对账

WITH ranked AS (
  SELECT
    ecosystem_code,
    package_name,
    version,
    published_at,
    is_default,
    row_number() OVER (
      PARTITION BY ecosystem_code, package_name
      ORDER BY
        CASE WHEN coalesce(is_default, false) THEN 0 ELSE 1 END,
        published_at DESC NULLS LAST,
        version DESC
    ) AS rn
  FROM dwd_dim_package_version
  WHERE dt = '${dt}'
)
SELECT *
FROM ranked
WHERE rn = 1;

SELECT
  d.from_ecosystem_code AS ecosystem_code,
  d.from_package_name   AS package_name,
  d.from_version        AS version,
  count(*)              AS direct_dep_cnt
FROM dwd_fact_dependency d
WHERE d.dt = '${dt}'
  AND d.relation = 'DIRECT'
GROUP BY 1, 2, 3;

SELECT
  r.ecosystem_code,
  r.package_name,
  coalesce(x.direct_dep_cnt, 0) AS direct_dep_cnt
FROM map_package_rep_version r
LEFT JOIN (
  SELECT from_ecosystem_code, from_package_name, from_version, count(*) AS direct_dep_cnt
  FROM dwd_fact_dependency
  WHERE dt = '${dt}' AND relation = 'DIRECT'
  GROUP BY 1, 2, 3
) x
  ON x.from_ecosystem_code = r.ecosystem_code
 AND x.from_package_name = r.package_name
 AND x.from_version = r.rep_version;

SELECT
  ecosystem_code,
  package_name,
  count(DISTINCT osv_id) AS high_vuln_affect_cnt
FROM dwd_fact_vuln_affects
WHERE dt = '${dt}'
  AND upper(coalesce(severity_level, 'UNKNOWN')) IN ('CRITICAL', 'HIGH')
  AND ecosystem_code IS NOT NULL
GROUP BY 1, 2
ORDER BY high_vuln_affect_cnt DESC
LIMIT 50;

SELECT
  package_name,
  cast(day AS date) AS day,
  downloads,
  sum(downloads) OVER (
    PARTITION BY package_name
    ORDER BY cast(day AS date)
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS download_cnt_7d
FROM dwd_fact_download_daily
WHERE ecosystem_code = 'npm';

SELECT
  to_ecosystem_code AS ecosystem_code,
  to_package_name   AS package_name,
  count(*)          AS depended_cnt_seed
FROM dwd_fact_dependency
WHERE dt = '${dt}'
  AND relation = 'DIRECT'
GROUP BY 1, 2
ORDER BY depended_cnt_seed DESC
LIMIT 50;

SELECT
  'dependency' AS table_name,
  count(*)     AS row_cnt
FROM dwd_fact_dependency
WHERE dt = '${dt}'
UNION ALL
SELECT 'vuln_affects', count(*)
FROM dwd_fact_vuln_affects
WHERE dt = '${dt}'
UNION ALL
SELECT 'download_daily', count(*)
FROM dwd_fact_download_daily
WHERE dt = '${dt}';

SELECT
  count(*) FILTER (WHERE ecosystem_code IS NOT NULL) * 1.0
  / nullif(count(*), 0) AS osv_map_ratio
FROM dwd_fact_vuln_affects
WHERE dt = '${dt}';

SELECT
  count(*) AS rows,
  count(DISTINCT osv_id) AS uniq_ids
FROM dwd_fact_vuln
WHERE dt = '${dt}';

SELECT
  p.ecosystem_code,
  p.package_name,
  p.rep_version,
  coalesce(d.direct_dep_cnt, 0) AS direct_dep_cnt,
  coalesce(h.high_vuln_affect_cnt, 0) AS high_vuln_affect_cnt,
  dl.download_cnt_7d
FROM map_package_rep_version p
LEFT JOIN /* M01 */ ... d
LEFT JOIN /* M03 */ ... h
LEFT JOIN /* M05 */ ... dl;