Admin (ID: 1)
头衔：论坛坛主
等级：究级天王[荣誉]
积分：231
发帖：14 篇
来自：保密
注册：2023-03-11 15:22:38
造访：2025-11-10 11:21:39
[ 第 13 楼 ]
回复
基础积分计算：
with defen as (
select a.actid, b.teamid1, b.teamid2, b.score1, b.score2, b.score1-b.score2 fencha
from activity a left join act5v5 b on b.actid=a.actid
where a.actdate>current_date-interval '2 months' and a.state>0 and b.score1!=b.score2
)
-- 数据拉直
, teamfen as (
select actid, teamid1 teamid, score1 score, fencha from defen union
select actid, teamid2 teamid, score2 score, -fencha fencha from defen
)
, jifenhis as (
select *, case
-- 1-9分，赢了得 600，输了得400
when abs(fencha)<10 then
case when fencha>0 then 600 else 400 end
-- 10-19分，赢了 700，输了300
when abs(fencha)<20 then
case when fencha>0 then 700 else 300 end
-- 20 分以上
else
case when fencha>0 then 800 else 200 end
end jifen from teamfen
)
select a.*, b.nick from (
select teamid, sum(jifen)+1000 jifen, count(0) actnum
from jifenhis group by teamid
) a left join teams b on b.teamid=a.teamid order by a.jifen desc
2024-05-31 15:21:26
IP：已设置保密
Admin (ID: 1)
头衔：论坛坛主
等级：究级天王[荣誉]
积分：231
发帖：14 篇
来自：保密
注册：2023-03-11 15:22:38
造访：2025-11-10 11:21:39
[ 第 14 楼 ]
回复
球队付费排名
with top10 as (
select teamid, count(0) paynum, round(sum(fee) / 100.0, 2) fee from act5v5_team_order
where paytime>date_trunc('year', current_date) and fee>0 and state>0
group by teamid order by fee desc limit 10
)
, actnum as (
select c.teamid, count(0) actnum
from activity a left join act5v5 b on b.actid=a.actid
left join top10 c on c.teamid in (b.teamid1, b.teamid2)
where a.actdate>=date_trunc('year', current_date) and a.state>0 and c.teamid>0
group by c.teamid
)
select c.nick 球队, b.actnum 比赛场次, a.paynum 付费场次, a.fee 总金额
from top10 a left join actnum b on b.teamid=a.teamid
left join teams c on c.teamid=a.teamid
2024-06-25 17:23:10
IP：已设置保密
Admin (ID: 1)
头衔：论坛坛主
等级：究级天王[荣誉]
积分：231
发帖：14 篇
来自：保密
注册：2023-03-11 15:22:38
造访：2025-11-10 11:21:39
[ 第 15 楼 ]
回复
个人付费排名
with top10 as (
select uid, count(0) paynum, round(sum(fee) / 100.0, 2) fee from payment
where paytime>date_trunc('year', current_date) and state>0
group by uid order by fee desc limit 10
)
select a.uid 用户ID, b.nick 昵称, paynum 付费次数, fee 总金额
from top10 a left join users b on b.uid=a.uid
2024-06-25 17:27:41
IP：已设置保密
Admin (ID: 1)
头衔：论坛坛主
等级：究级天王[荣誉]
积分：231
发帖：14 篇
来自：保密
注册：2023-03-11 15:22:38
造访：2025-11-10 11:21:39
[ 第 16 楼 ]
回复
2.0 无效标注场馆排名：
with allact as (
select a.actid, a.courtid, b.teamid1, b.teamid2, b.public1, b.public2
from activity a left join act5v5 b on b.actid=a.actid
where a.actdate>current_date-interval '2 months' and a.ext->'checkUser' is not null and a.ext->>'assist'='1'
)
, sumcourt as (
select courtid, count(0) 标注场数, sum(case public1 when 0 then 0 else 1 end + case public2 when 0 then 0 else 1 end) 解锁球队, count(case public1+public2 when 0 then 0 else null end) 完全没解锁场数
from allact group by courtid
)
select a.*, b.courtname,
round(完全没解锁场数 * 100.0 / 标注场数, 1) || '%' 无效标注占比,
round(100 - (解锁球队 * 50.0 / 标注场数), 1) || '%' 球队未解锁率
from sumcourt a left join court b on b.courtid=a.courtid order by 完全没解锁场数 * 100.0 / 标注场数 desc
2024-08-07 10:44:17
IP：已设置保密
Admin (ID: 1)
头衔：论坛坛主
等级：究级天王[荣誉]
积分：231
发帖：14 篇
来自：保密
注册：2023-03-11 15:22:38
造访：2025-11-10 11:21:39
[ 第 17 楼 ]
回复
SQL-月度城市公司对账信息
with arg1 as (select '2025-01-01'::timestamp stime)
, arg2 as (select *, stime + interval '1 month' - interval '1 second' etime from arg1)
, arg3 as (
select 'VipCard' api, '会员卡' intro union
select 'UnlockStar', '球员报告' union
select 'CourtUnlockTeam', '场馆解锁报告' union
select 'UnlockTeam', '球队报告' union
select 'UnlockTeamFusion', '仅球队集锦'
), pays as (
select api, cityid, fee * 0.01 cash
from arg2 a left join payment b on b.paytime between a.stime and a.etime
)
select a.intro "业态",
sum(case cityid when 1 then cash else 0 end) "长沙",
sum(case cityid when 2 then cash else 0 end) "深圳",
sum(case cityid when 3 then cash else 0 end) "杭州",
sum(case cityid when 4 then cash else 0 end) "海南"
from arg3 a
left join pays b on b.api=a.api
group by a.intro
2025-02-08 11:07:47
IP：已设置保密
Admin (ID: 1)
头衔：论坛坛主
等级：究级天王[荣誉]
积分：231
发帖：14 篇
来自：保密
注册：2023-03-11 15:22:38
造访：2025-11-10 11:21:39
[ 第 18 楼 ]
回复
和比赛相关的收入统计查询
-- 和比赛相关的收入统计查询
with unlockteam as (
select payid, fee, (string_to_array(ext::json->>'attach', '.'))[1]::integer actid, to_char(paytime, 'YYYY-MM') paymon
from payment where api in ('UnlockTeam', 'UnlockTeamFusion', 'CourtUnlockTeam') and state=1
),
unlockstar as (
select payid, fee, refid actid, to_char(paytime, 'YYYY-MM') paymon from payment where api='UnlockStar' and state=1
)
select a.paymon, sum(case b.placeid when 149 then a.fee else 0 end) * 0.01 "场地一",
sum(case b.placeid when 150 then a.fee else 0 end) * 0.01 "场地二" from (
select * from unlockstar a union
select * from unlockteam b
) a left join activity b on b.actid=a.actid
where b.courtid=82 group by paymon
2025-07-08 14:03:02
IP：已设置保密
