8d1eef2f7f
1. 排除商城直购(source_type=1)：GMV和成本过滤条件从IN(1,2,3,4)改为IN(2,3,4) 2. 排除次卡免费使用订单(actual_amount=0)：避免购买次卡和使用次卡双重计入GMV - source_type=4 一番赏使用次卡：1578单 44032元重复 - source_type=3 对对碰使用次卡：422单 7042元重复 - 合计去除51074元虚增GMV（29.1%） 3. 成本过滤条件同步修正：source_type IN(2,3,4)，total_amount>0 修正后：GMV从175600降至124527元，毛利率从37.4%回到真实的11.8%
62 lines
2.0 KiB
Go
62 lines
2.0 KiB
Go
package main
|
|
|
|
import (
|
|
"fmt"
|
|
|
|
"gorm.io/driver/mysql"
|
|
"gorm.io/gorm"
|
|
)
|
|
|
|
func main() {
|
|
dsn := "root:bindbox2025kdy@tcp(150.158.78.154:3306)/dev_game?charset=utf8mb4&parseTime=True&loc=Local"
|
|
db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{})
|
|
|
|
channelID := 3
|
|
filter := "users.channel_id = ? AND users.deleted_at IS NULL AND orders.status = 2 AND orders.actual_amount > 0 AND orders.source_type IN (1,2,3,4) AND (orders.ext_order_id = '' OR orders.ext_order_id IS NULL)"
|
|
|
|
type SourceStat struct {
|
|
SourceType int32
|
|
HasRemark string
|
|
Count int64
|
|
TotalCents int64
|
|
}
|
|
var stats []SourceStat
|
|
db.Table("orders").
|
|
Joins("JOIN users ON users.id = orders.user_id").
|
|
Select("orders.source_type, CASE WHEN orders.remark LIKE '%lottery:activity:%' OR orders.remark LIKE '%activity:%' THEN 'Y' ELSE 'N' END as has_remark, COUNT(*) as count, SUM(orders.actual_amount) as total_cents").
|
|
Where(filter, channelID).
|
|
Group("orders.source_type, has_remark").
|
|
Order("orders.source_type, has_remark").
|
|
Scan(&stats)
|
|
|
|
fmt.Println("source_type: 1=直购, 2=抽奖, 3=翻牌, 4=一番赏")
|
|
fmt.Printf("%-12s %-12s %-10s %-15s\n", "source_type", "有remark", "订单数", "actual_amount(分)")
|
|
fmt.Println("---------------------------------------------------")
|
|
for _, s := range stats {
|
|
fmt.Printf("%-12d %-12s %-10d %-15d\n", s.SourceType, s.HasRemark, s.Count, s.TotalCents)
|
|
}
|
|
|
|
type Sample struct {
|
|
ID int64
|
|
SourceType int32
|
|
ActualAmount int64
|
|
Remark string
|
|
}
|
|
var samples []Sample
|
|
db.Table("orders").
|
|
Joins("JOIN users ON users.id = orders.user_id").
|
|
Select("orders.id, orders.source_type, orders.actual_amount, orders.remark").
|
|
Where(filter+" AND (orders.remark = '' OR orders.remark NOT LIKE '%activity:%')", channelID).
|
|
Limit(10).
|
|
Scan(&samples)
|
|
|
|
fmt.Println("\n无 activity remark 的订单示例:")
|
|
for _, s := range samples {
|
|
rmk := s.Remark
|
|
if len(rmk) > 80 {
|
|
rmk = rmk[:80] + "..."
|
|
}
|
|
fmt.Printf(" ID=%-6d type=%d amount=%-8d remark=[%s]\n", s.ID, s.SourceType, s.ActualAmount, rmk)
|
|
}
|
|
}
|