64 lines
2.0 KiB
Python
64 lines
2.0 KiB
Python
"""验证 SCD2 修复后 assistant_level_name NULL 情况"""
|
||
import os, sys
|
||
from pathlib import Path
|
||
from dotenv import load_dotenv
|
||
|
||
load_dotenv(Path(__file__).resolve().parents[2] / ".env")
|
||
|
||
import psycopg2
|
||
import psycopg2.extras
|
||
|
||
dsn = os.environ.get("PG_DSN")
|
||
if not dsn:
|
||
raise RuntimeError("PG_DSN 未设置")
|
||
|
||
conn = psycopg2.connect(dsn)
|
||
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
||
|
||
# 1. daily_detail 中 NULL level_name 统计
|
||
cur.execute("""
|
||
SELECT
|
||
COUNT(*) AS total,
|
||
COUNT(*) FILTER (WHERE assistant_level_name IS NULL) AS null_name,
|
||
COUNT(*) FILTER (WHERE assistant_level_name IS NOT NULL) AS non_null_name
|
||
FROM dws.dws_assistant_daily_detail
|
||
""")
|
||
row = cur.fetchone()
|
||
print("=== dws_assistant_daily_detail ===")
|
||
print(f" 总行数: {row['total']}")
|
||
print(f" level_name NULL: {row['null_name']}")
|
||
print(f" level_name 非NULL: {row['non_null_name']}")
|
||
|
||
# 2. monthly_summary 中 NULL level_name 统计
|
||
cur.execute("""
|
||
SELECT
|
||
COUNT(*) AS total,
|
||
COUNT(*) FILTER (WHERE assistant_level_name IS NULL) AS null_name,
|
||
COUNT(*) FILTER (WHERE assistant_level_name IS NOT NULL) AS non_null_name
|
||
FROM dws.dws_assistant_monthly_summary
|
||
""")
|
||
row = cur.fetchone()
|
||
print("\n=== dws_assistant_monthly_summary ===")
|
||
print(f" 总行数: {row['total']}")
|
||
print(f" level_name NULL: {row['null_name']}")
|
||
print(f" level_name 非NULL: {row['non_null_name']}")
|
||
|
||
# 3. 如果 daily 还有 NULL，看看是哪些
|
||
cur.execute("""
|
||
SELECT assistant_id, stat_date, assistant_level_code, assistant_level_name
|
||
FROM dws.dws_assistant_daily_detail
|
||
WHERE assistant_level_name IS NULL
|
||
ORDER BY stat_date DESC
|
||
LIMIT 10
|
||
""")
|
||
rows = cur.fetchall()
|
||
if rows:
|
||
print("\n=== daily NULL level_name 样本 ===")
|
||
for r in rows:
|
||
print(f" assistant_id={r['assistant_id']}, date={r['stat_date']}, code={r['assistant_level_code']}")
|
||
else:
|
||
print("\n✅ daily_detail 中无 NULL level_name")
|
||
|
||
cur.close()
|
||
conn.close()
|