56 lines
2.1 KiB
Python
56 lines
2.1 KiB
Python
"""DEBUG 第二轮：meta.etl_task 和 consume_money 来源。"""
|
||
import psycopg2, psycopg2.extras, os
|
||
from dotenv import load_dotenv
|
||
from pathlib import Path
|
||
|
||
load_dotenv(Path(__file__).resolve().parents[2] / ".env")
|
||
conn = psycopg2.connect(os.environ["PG_DSN"])
|
||
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
|
||
|
||
print("=== 1. meta.etl_task 中 ODS_STAFF_INFO ===")
|
||
cur.execute("SELECT task_code, store_id, enabled FROM meta.etl_task WHERE task_code = 'ODS_STAFF_INFO'")
|
||
rows = cur.fetchall()
|
||
if rows:
|
||
for r in rows:
|
||
print(f" store={r['store_id']}, enabled={r['enabled']}")
|
||
else:
|
||
print(" 不存在！未在 meta.etl_task 中注册 → 这就是被跳过的原因")
|
||
|
||
print("\n=== 2. consume_money 来源追溯 ===")
|
||
# member_consumption_task 的 SQL 用了 consume_base CTE，来源是 dwd_settlement_head
|
||
# 查 consume_money 列是否存在
|
||
cur.execute("""
|
||
SELECT column_name FROM information_schema.columns
|
||
WHERE table_schema='dwd' AND table_name='dwd_settlement_head'
|
||
AND column_name = 'consume_money'
|
||
""")
|
||
has_col = bool(cur.fetchall())
|
||
print(f" dwd_settlement_head.consume_money 存在: {has_col}")
|
||
|
||
# 查该会员的结算记录
|
||
member_id = 2799207378798341
|
||
site_id = 2790685415443269
|
||
|
||
cur.execute("""
|
||
SELECT SUM(pay_amount) as total_pay, COUNT(*) as cnt,
|
||
MIN(pay_amount) as min_pay, MAX(pay_amount) as max_pay
|
||
FROM dwd.dwd_settlement_head
|
||
WHERE member_id = %s AND site_id = %s
|
||
""", (member_id, site_id))
|
||
r = cur.fetchone()
|
||
print(f" settlement_head: {r['cnt']} 条, SUM(pay_amount)={r['total_pay']}, min={r['min_pay']}, max={r['max_pay']}")
|
||
|
||
# 查负值的 pay_amount 记录
|
||
cur.execute("""
|
||
SELECT settlement_id, pay_amount, settle_date, settle_type
|
||
FROM dwd.dwd_settlement_head
|
||
WHERE member_id = %s AND site_id = %s AND pay_amount < 0
|
||
ORDER BY pay_amount LIMIT 5
|
||
""", (member_id, site_id))
|
||
neg_rows = cur.fetchall()
|
||
print(f"\n 负值 pay_amount 记录: {len(neg_rows)} 条")
|
||
for r in neg_rows:
|
||
print(f" id={r['settlement_id']}, pay={r['pay_amount']}, date={r['settle_date']}, type={r['settle_type']}")
|
||
|
||
conn.close()
|