75 lines
2.4 KiB
Python
75 lines
2.4 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)
|
|
|
|
# 检查dim_assistant表结构
|
|
print('=== dim_assistant columns ===')
|
|
sql0 = """
|
|
SELECT column_name FROM information_schema.columns
|
|
WHERE table_schema = 'billiards_dwd' AND table_name = 'dim_assistant'
|
|
"""
|
|
for row in db.query(sql0):
|
|
print(f' {dict(row)["column_name"]}')
|
|
|
|
# 检查dim_assistant数量
|
|
print()
|
|
print('=== dim_assistant ===')
|
|
sql1 = 'SELECT COUNT(*) as cnt FROM billiards_dwd.dim_assistant WHERE scd2_is_current = 1'
|
|
rows = db.query(sql1)
|
|
print(f'dim_assistant current count: {dict(rows[0])["cnt"]}')
|
|
|
|
# 检查服务记录中的nickname分布
|
|
print()
|
|
print('=== Service by nickname ===')
|
|
sql2 = """
|
|
SELECT nickname, COUNT(*) as service_count, 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 nickname
|
|
ORDER BY service_count DESC
|
|
LIMIT 10
|
|
"""
|
|
for row in db.query(sql2):
|
|
r = dict(row)
|
|
print(f' {r["nickname"]}: {r["service_count"]} services, {r["member_count"]} members')
|
|
|
|
# 检查assistant_no分布
|
|
print()
|
|
print('=== Service by assistant_no ===')
|
|
sql3 = """
|
|
SELECT assistant_no, nickname, COUNT(*) as service_count, 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 assistant_no, nickname
|
|
ORDER BY service_count DESC
|
|
LIMIT 10
|
|
"""
|
|
for row in db.query(sql3):
|
|
r = dict(row)
|
|
print(f' {r["assistant_no"]} ({r["nickname"]}): {r["service_count"]} services, {r["member_count"]} members')
|
|
|
|
# 近60天
|
|
print()
|
|
print('=== Last 60 days by nickname ===')
|
|
sql4 = """
|
|
SELECT nickname, COUNT(*) as service_count, COUNT(DISTINCT tenant_member_id) as member_count
|
|
FROM billiards_dwd.dwd_assistant_service_log
|
|
WHERE tenant_member_id > 0 AND is_delete = 0
|
|
AND last_use_time >= NOW() - INTERVAL '60 days'
|
|
GROUP BY nickname
|
|
ORDER BY service_count DESC
|
|
LIMIT 15
|
|
"""
|
|
for row in db.query(sql4):
|
|
r = dict(row)
|
|
print(f' {r["nickname"]}: {r["service_count"]} services, {r["member_count"]} members')
|
|
|
|
db_conn.close()
|