82 lines
2.5 KiB
Python
82 lines
2.5 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""列出指数表数据"""
|
|
import sys
|
|
import io
|
|
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')
|
|
sys.path.insert(0, '.')
|
|
from config.settings import AppConfig
|
|
from database.connection import DatabaseConnection
|
|
from database.operations import DatabaseOperations
|
|
|
|
config = AppConfig.load()
|
|
db_conn = DatabaseConnection(config.config['db']['dsn'])
|
|
db = DatabaseOperations(db_conn)
|
|
|
|
# ============================================================
|
|
# 1. 客户召回表
|
|
# ============================================================
|
|
print("=" * 60)
|
|
print("1. 客户召回表")
|
|
print("=" * 60)
|
|
print(f"{'客户姓名':<20} | {'召回指数':>8}")
|
|
print("-" * 60)
|
|
|
|
sql_recall = """
|
|
SELECT
|
|
COALESCE(m.nickname, CONCAT('会员', r.member_id)) AS member_name,
|
|
r.display_score AS recall_score
|
|
FROM billiards_dws.dws_member_recall_index r
|
|
LEFT JOIN billiards_dwd.dim_member m
|
|
ON r.member_id = m.member_id AND m.scd2_is_current = 1
|
|
ORDER BY r.display_score DESC
|
|
"""
|
|
|
|
rows = db.query(sql_recall)
|
|
for row in rows:
|
|
r = dict(row)
|
|
name = r['member_name'] or '未知'
|
|
score = r['recall_score']
|
|
print(f"{name:<20} | {score:>8.2f}")
|
|
|
|
print()
|
|
print(f"共 {len(rows)} 条记录")
|
|
|
|
# ============================================================
|
|
# 2. 助教客户关系表
|
|
# ============================================================
|
|
print()
|
|
print("=" * 60)
|
|
print("2. 助教客户关系表")
|
|
print("=" * 60)
|
|
print(f"{'助教花名':<12} | {'客户姓名':<20} | {'关系指数':>8}")
|
|
print("-" * 60)
|
|
|
|
sql_intimacy = """
|
|
SELECT
|
|
a.nickname AS assistant_name,
|
|
i.assistant_id AS assistant_no,
|
|
COALESCE(m.nickname, CONCAT('会员', i.member_id)) AS member_name,
|
|
i.display_score AS intimacy_score,
|
|
i.session_count,
|
|
i.attributed_recharge_amount
|
|
FROM billiards_dws.dws_member_assistant_intimacy i
|
|
LEFT JOIN billiards_dwd.dim_member m
|
|
ON i.member_id = m.member_id AND m.scd2_is_current = 1
|
|
LEFT JOIN billiards_dwd.dim_assistant a
|
|
ON i.assistant_id::text = a.assistant_no AND a.scd2_is_current = 1
|
|
ORDER BY i.display_score DESC, i.session_count DESC
|
|
"""
|
|
|
|
rows2 = db.query(sql_intimacy)
|
|
for row in rows2:
|
|
r = dict(row)
|
|
assistant = r['assistant_name'] or f"工号{r.get('assistant_no', '?')}"
|
|
member = r['member_name'] or '未知'
|
|
score = r['intimacy_score']
|
|
print(f"{assistant:<12} | {member:<20} | {score:>8.2f}")
|
|
|
|
print()
|
|
print(f"共 {len(rows2)} 条记录")
|
|
|
|
db_conn.close()
|