43 lines
2.2 KiB
SQL
43 lines
2.2 KiB
SQL
-- 每日明细
|
|
SELECT DATE_FORMAT(ptime, '%Y%m') '年月', DATE_FORMAT(ptime, '%d') '日', membership_cnt '会员数', membership_flow '会员流水', zone_admission_cnt '空间解锁数', zone_admission_flow '空间解锁流水', zone_superfan_cnt '空间超粉数', zone_superfan_flow '空间超粉流水', zone_moment_cnt '空间动态数', zone_moment_flow '空间动态流水', other_cnt '其他数(微信+金币)', other_flow '其他流水(微信+金币)', total_flow '总流水', streamer_flow '主播分成', official_flow '净收入', CONCAT(ROUND(profit_rate * 100.0, 2), '% ') '利润率', CONCAT(ROUND(membership_rate * 100.0, 2), '% ') '会员流水占比', CONCAT(ROUND(zone_admission_rate * 100.0, 2), '% ') '空间解锁占比', CONCAT(ROUND(zone_superfan_rate * 100.0, 2), '% ') '空间超粉占比', CONCAT(ROUND(zone_moment_rate * 100.0, 2), '% ') '空间动态占比', CONCAT(ROUND(other_rate * 100.0, 2), '% ') '其他占比 '
|
|
FROM vas_day_detail
|
|
ORDER BY ptime DESC
|
|
LIMIT 100;
|
|
|
|
-- 时分同比
|
|
SELECT t2.t AS '时间',
|
|
IFNULL(t2.flow / 100, 0) AS '前天',
|
|
IFNULL(t1.flow / 100, 0) AS '昨天',
|
|
IFNULL(t0.flow / 100, 0) As '今天'
|
|
FROM (
|
|
SELECT DATE_FORMAT(ptime, '%H:%i:%s') AS t, flow
|
|
FROM vas_yoy_flow
|
|
WHERE ptime>=DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, '%Y-%m-%d 00:00:00') AND ptime<DATE_FORMAT(CURDATE() - INTERVAL 1 DAY , '%Y-%m-%d 00:00:00')
|
|
) t2
|
|
LEFT JOIN (
|
|
SELECT DATE_FORMAT(ptime, '%H:%i:%s') AS t, flow
|
|
FROM vas_yoy_flow
|
|
WHERE ptime>=DATE_FORMAT(CURDATE() - INTERVAL 1 DAY, '%Y-%m-%d 00:00:00') AND ptime<DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')
|
|
) t1
|
|
ON t1.t=t2.t
|
|
LEFT JOIN (
|
|
SELECT DATE_FORMAT(ptime, '%H:%i:%s') AS t, flow
|
|
FROM vas_yoy_flow
|
|
WHERE ptime>=DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')
|
|
) t0
|
|
ON t0.t=t2.t;
|
|
|
|
-- 主播收入明细
|
|
SELECT t1.mid AS '主播mid', t2.user_id AS '主播user_id', t2.name AS '昵称', t1.income AS '主播收入(钻石)'
|
|
FROM (
|
|
SELECT mid, income, pdate
|
|
FROM vas_cur_streamer_income
|
|
WHERE pdate = DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')
|
|
) t1
|
|
LEFT JOIN(
|
|
SELECT mid, user_id, name FROM streamer
|
|
) t2
|
|
ON t1.mid=t2.mid
|
|
WHERE t2.name!=''
|
|
ORDER BY t1.income DESC
|