81 lines
2.7 KiB
Python
81 lines
2.7 KiB
Python
"""DEBUG 第三轮：负值会员完整数据链。"""
|
|
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)
|
|
|
|
member_id = 2799207378798341
|
|
site_id = 2790685415443269
|
|
|
|
# 查 settlement_head 的 PK 列和金额列
|
|
cur.execute("""
|
|
SELECT column_name FROM information_schema.columns
|
|
WHERE table_schema='dwd' AND table_name='dwd_settlement_head'
|
|
ORDER BY ordinal_position LIMIT 10
|
|
""")
|
|
print("=== dwd_settlement_head 前10列 ===")
|
|
for r in cur.fetchall():
|
|
print(f" {r['column_name']}")
|
|
|
|
# 查 consume_money
|
|
cur.execute("""
|
|
SELECT SUM(consume_money) as total_consume, COUNT(*) as cnt,
|
|
MIN(consume_money) as min_cm, MAX(consume_money) as max_cm
|
|
FROM dwd.dwd_settlement_head
|
|
WHERE member_id = %s AND site_id = %s
|
|
""", (member_id, site_id))
|
|
r = cur.fetchone()
|
|
print(f"\n=== 该会员 consume_money 汇总 ===")
|
|
print(f" {r['cnt']} 条, SUM={r['total_consume']}, min={r['min_cm']}, max={r['max_cm']}")
|
|
|
|
# 查负值 consume_money 记录
|
|
cur.execute("""
|
|
SELECT consume_money, pay_amount, create_time, member_name
|
|
FROM dwd.dwd_settlement_head
|
|
WHERE member_id = %s AND site_id = %s AND consume_money < 0
|
|
ORDER BY consume_money LIMIT 5
|
|
""", (member_id, site_id))
|
|
rows = cur.fetchall()
|
|
print(f"\n=== 负值 consume_money 记录 ({len(rows)} 条) ===")
|
|
for r in rows:
|
|
print(f" consume={r['consume_money']}, pay={r['pay_amount']}, "
|
|
f"date={r['create_time']}, name={r['member_name']}")
|
|
|
|
# 查全部记录
|
|
cur.execute("""
|
|
SELECT consume_money, pay_amount, create_time
|
|
FROM dwd.dwd_settlement_head
|
|
WHERE member_id = %s AND site_id = %s
|
|
ORDER BY create_time
|
|
""", (member_id, site_id))
|
|
rows = cur.fetchall()
|
|
print(f"\n=== 全部结算记录 ({len(rows)} 条) ===")
|
|
for r in rows:
|
|
print(f" date={r['create_time']}, consume={r['consume_money']}, pay={r['pay_amount']}")
|
|
|
|
# ODS 层原始数据
|
|
cur.execute("""
|
|
SELECT column_name FROM information_schema.columns
|
|
WHERE table_schema='ods' AND table_name='settlement_records'
|
|
AND column_name LIKE '%%consume%%'
|
|
""")
|
|
ods_cols = [r['column_name'] for r in cur.fetchall()]
|
|
print(f"\n=== ods.settlement_records consume 列: {ods_cols} ===")
|
|
|
|
if ods_cols:
|
|
col = ods_cols[0]
|
|
cur.execute(f"""
|
|
SELECT {col}, id FROM ods.settlement_records
|
|
WHERE tenant_member_id = %s AND site_id = %s AND {col} < 0
|
|
ORDER BY {col} LIMIT 5
|
|
""", (member_id, site_id))
|
|
rows = cur.fetchall()
|
|
print(f" ODS 负值记录: {len(rows)} 条")
|
|
for r in rows:
|
|
print(f" {col}={r[col]}, id={r['id']}")
|
|
|
|
conn.close()
|