83 lines
2.8 KiB
Python
83 lines
2.8 KiB
Python
# -*- coding: utf-8 -*-
|
|
import sys
|
|
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)
|
|
|
|
# 检查DWD层服务记录分布
|
|
print("=== DWD层服务记录分析 ===")
|
|
print()
|
|
|
|
# 1. 总体统计
|
|
sql1 = """
|
|
SELECT
|
|
COUNT(*) as total_records,
|
|
COUNT(DISTINCT tenant_member_id) as unique_members,
|
|
COUNT(DISTINCT site_assistant_id) as unique_assistants,
|
|
COUNT(DISTINCT (tenant_member_id, site_assistant_id)) as unique_pairs
|
|
FROM billiards_dwd.dwd_assistant_service_log
|
|
WHERE tenant_member_id > 0 AND is_delete = 0
|
|
"""
|
|
r = dict(db.query(sql1)[0])
|
|
print("总体统计:")
|
|
print(f" 总服务记录数: {r['total_records']}")
|
|
print(f" 唯一会员数: {r['unique_members']}")
|
|
print(f" 唯一助教数: {r['unique_assistants']}")
|
|
print(f" 唯一客户-助教对: {r['unique_pairs']}")
|
|
|
|
# 2. 助教服务会员数分布
|
|
print()
|
|
print("助教服务会员数分布 (Top 10):")
|
|
sql2 = """
|
|
SELECT site_assistant_id, COUNT(DISTINCT tenant_member_id) as member_count
|
|
FROM billiards_dwd.dwd_assistant_service_log
|
|
WHERE tenant_member_id > 0 AND is_delete = 0
|
|
GROUP BY site_assistant_id
|
|
ORDER BY member_count DESC
|
|
LIMIT 10
|
|
"""
|
|
for row in db.query(sql2):
|
|
r = dict(row)
|
|
print(f" 助教 {r['site_assistant_id']}: 服务 {r['member_count']} 个会员")
|
|
|
|
# 3. 每个客户-助教对的服务次数分布
|
|
print()
|
|
print("客户-助教对 服务次数分布 (Top 10):")
|
|
sql3 = """
|
|
SELECT tenant_member_id, site_assistant_id, COUNT(*) as service_count
|
|
FROM billiards_dwd.dwd_assistant_service_log
|
|
WHERE tenant_member_id > 0 AND is_delete = 0
|
|
GROUP BY tenant_member_id, site_assistant_id
|
|
ORDER BY service_count DESC
|
|
LIMIT 10
|
|
"""
|
|
for row in db.query(sql3):
|
|
r = dict(row)
|
|
print(f" 会员 {r['tenant_member_id']} - 助教 {r['site_assistant_id']}: {r['service_count']} 次服务")
|
|
|
|
# 4. 近60天的数据
|
|
print()
|
|
print("=== 近60天数据 ===")
|
|
sql4 = """
|
|
SELECT
|
|
COUNT(*) as total_records,
|
|
COUNT(DISTINCT tenant_member_id) as unique_members,
|
|
COUNT(DISTINCT site_assistant_id) as unique_assistants,
|
|
COUNT(DISTINCT (tenant_member_id, site_assistant_id)) as unique_pairs
|
|
FROM billiards_dwd.dwd_assistant_service_log
|
|
WHERE tenant_member_id > 0 AND is_delete = 0
|
|
AND last_use_time >= NOW() - INTERVAL '60 days'
|
|
"""
|
|
r4 = dict(db.query(sql4)[0])
|
|
print(f" 总服务记录数: {r4['total_records']}")
|
|
print(f" 唯一会员数: {r4['unique_members']}")
|
|
print(f" 唯一助教数: {r4['unique_assistants']}")
|
|
print(f" 唯一客户-助教对: {r4['unique_pairs']}")
|
|
|
|
db_conn.close()
|