select
substr(DATE_FORMAT(trx_time, '%Y-%m-%d'),1,7) as trx_mon
,max(last_day(trx_time)) as last_day
from
cmb_usr_trx_rcd
where
year(trx_time) in (2023,2024)
group by
substr(DATE_FORMAT(trx_time, '%Y-%m-%d'),1,7)
order by
trx_mon
我想问一个语法方面的问题,就是有了group分组之后,是不是输出的列也是要聚合好的。我一开始lastday那里没有加max,就是无法输出。
select a,b from t group by a会报错的;select a, max(b) from t group by a不会报错。你的语句不加max,就是犯了第一种错
select
trx_amt
,count(trx_amt) as total_trx_cnt
,count(distinct usr_id) as unique_usr_cnt
,count(trx_amt)/count(distinct usr_id) as avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm='红玫瑰按摩保健休闲'
and year(trx_time)=2023 and month(trx_time) in (1,2,3,4,5,6,7,8,9,10,11,12)
or year(trx_time)=2024 and month(trx_time) in (1,2,3,4,5,6)
group by trx_amt
order by avg_trx_per_user desc
limit 5
我输出的结果中total_trx_cnt 数量不对,288.888,1288都多了两个
select
usr_id
, sum(trx_amt) as sum_trx_amt
from
cmb_usr_trx_rcd
where
mch_nm = '红玫瑰按摩保健休闲'
and year(trx_time) between 2023 and 2024
group by usr_id
order by 2 desc
limit 10
select
usr_id
,sum(trx_amt)
from
cmb_usr_trx_rcd
where
mch_nm='红玫瑰按摩保健休闲'
and year(trx_time) in (2022,2023)
group by
usr_id
order by
sum(trx_amt) desc
limit
5
SELECT
YEAR(trx_time) AS Y,
month(trx_time) AS m,
SUM(trx_amt) AS sum_trx_amt
FROM
cmb_usr_trx_rcd
WHERE
usr_id = 5201314520
AND YEAR(trx_time) IN (2022, 2023, 2024)
AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY
YEAR(trx_time),
month(trx_time)
ORDER BY
YEAR(trx_time),
month(trx_time)
SELECT
YEAR(trx_time) AS Y,
SUBSTR(DATE_FORMAT(trx_time, '%Y-%m'), 6, 2) AS m,
SUM(trx_amt) AS sum_trx_amt
FROM
cmb_usr_trx_rcd
WHERE
usr_id = 5201314520
AND YEAR(trx_time) IN (2022, 2023, 2024)
AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY
YEAR(trx_time),
SUBSTR(DATE_FORMAT(trx_time, '%Y-%m'), 6, 2)
ORDER BY
YEAR(trx_time),
SUBSTR(DATE_FORMAT(trx_time, '%Y-%m'), 6, 2);
select
year(trx_time) as Y
,sum(trx_amt) as sum_trx_amt
from
cmb_usr_trx_rcd
where
usr_id=5201314520
and year(trx_time) in (2022,2023,2024)
and mch_nm ='红玫瑰按摩保健休闲'
group by
year(trx_time)
order by
1
select
*
from
cmb_usr_trx_rcd
where
usr_id=5201314520
and year(trx_time) in (2022,2023,2024)
and day(trx_time) in (28,29,30,31,1)
and trx_amt >300
order by
trx_time
select
*
from
cmb_usr_trx_rcd
where
date_format(trx_time,'%Y-%m-%d') between '2024-06-08' and '2024-06-10'
and
hour(trx_time) in (11,12,18,19)
and
mch_nm='红玫瑰按摩保健休闲'
WITH monthly_trx AS (
SELECT
t1.trx_mon,
COALESCE(t2.trx_amt, 0) AS monthly_trx_amt
FROM
(
SELECT
DISTINCT DATE_FORMAT(date_value, '%Y-%m') AS trx_mon
FROM
date_table
WHERE
YEAR(date_value) = 2023
) AS t1
LEFT JOIN
(
SELECT
DATE_FORMAT(a.trx_time, '%Y-%m') AS trx_mon,
SUM(a.trx_amt) AS trx_amt
FROM
cmb_usr_trx_rcd AS a
JOIN cmb_mch_typ AS b ON a.mch_nm = b.mch_nm
WHERE
a.usr_id = 5201314520
AND b.mch_typ = '休闲娱乐'
AND YEAR(a.trx_time) = 2023
GROUP BY
DATE_FORMAT(a.trx_time, '%Y-%m')
) AS t2
ON t1.trx_mon = t2.trx_mon
),
cumulative_month AS (
SELECT
trx_mon,
monthly_trx_amt,
SUM(monthly_trx_amt) OVER (ORDER BY trx_mon) AS cumulative_trx_amt
FROM
monthly_trx
)
SELECT
trx_mon,
cumulative_trx_amt AS trx_amt
FROM
cumulative_month
ORDER BY
trx_mon;
WITH monthly_trx AS (
SELECT
DATE_FORMAT(t1.trx_time, '%Y-%m') AS trx_mon,
SUM(t1.trx_amt) AS monthly_trx_amt
FROM
cmb_usr_trx_rcd AS t1
JOIN
cmb_mch_typ AS t2 ON t1.mch_nm = t2.mch_nm
WHERE
t1.usr_id = 5201314520
AND t2.mch_typ = '休闲娱乐'
AND DATE_FORMAT(t1.trx_time, '%Y-%m') BETWEEN '2023-01' AND '2024-12'
GROUP BY
DATE_FORMAT(t1.trx_time, '%Y-%m')
),
cumulative_trx AS (
SELECT
trx_mon,
monthly_trx_amt,
SUM(monthly_trx_amt) OVER (ORDER BY trx_mon) AS cumulative_trx_amt
FROM
monthly_trx
)
SELECT
trx_mon,
cumulative_trx_amt AS trx_amt
FROM
cumulative_trx
ORDER BY
trx_mon;
select
distinct date_format(trx_time,'%Y-%m') astrx_mon
,sum(t1.trx_amt)over(partition by date_format(trx_time,'%Y-%m') ) as trx_amt
from
cmb_usr_trx_rcd as t1
join
cmb_mch_typ as t2
on t1.mch_nm=t2.mch_nm
where
t1.usr_id=5201314520
and
t2.mch_typ='休闲娱乐'
and
date_format(trx_time,'%Y-%m') between '2023-01' and '2024-12'
select
date_format(trx_time,'%Y-%m') astrx_mon
,sum(t1.trx_amt)over(partition by date_format(trx_time,'%Y-%m') ) as trx_amt
from
cmb_usr_trx_rcd as t1
join
cmb_mch_typ as t2
on t1.mch_nm=t2.mch_nm
where
t1.usr_id=5201314520
and
t2.mch_typ='休闲娱乐'
and
date_format(trx_time,'%Y-%m') between '2023-01' and '2024-12'
with
t1 as
(
select
'all' as mch_typ
,mch_nm
,count(trx_time) as trx_cnt
,dense_rank()over(order by count(trx_time) desc) as rnk
from
cmb_usr_trx_rcd
where
usr_id=5201314520
group by
mch_nm
),
t2 as
(
select
s2.mch_typ
,s1.mch_nm
,count(s1.trx_time) as trx_cnt
,dense_rank()over(partition by s2.mch_typ order by count(s1.trx_time)desc) as rnk
from
cmb_usr_trx_rcd as s1
inner join
cmb_mch_typ as s2
on s1.mch_nm=s2.mch_nm
where
s1.usr_id=5201314520
and
s2.mch_typ in ('交通出行','休闲娱乐','咖啡奶茶')
group by
s2.mch_typ
,s1.mch_nm
),
filtered_t2 as
(
select
mch_typ
,mch_nm
,trx_cnt
,rnk
from
t2
where
rnk<=1
),
filtered_t1 as
(
select
mch_typ
,mch_nm
,trx_cnt
,rnk
from
t1
where
rnk<=1
)
select
mch_typ
,mch_nm
,trx_cnt
,rnk
from
filtered_t1
union all
select
mch_typ
,mch_nm
,trx_cnt
,rnk
from
filtered_t2
order by
mch_typ,
rnk,
mch_nm,
trx_cnt
with
t1 as
(
select
'all' as mch_typ
,mch_nm
,count(trx_time) as trx_cnt
,dense_rank()over(order by count(trx_time) desc) as rnk
from
cmb_usr_trx_rcd
where
usr_id=5201314520
group by
mch_nm
),
t2 as
(
select
s2.mch_typ
,s1.mch_nm
,count(s1.trx_time) as trx_cnt
,dense_rank()over(partition by s2.mch_typ order by count(s1.trx_time)desc) as rnk
from
cmb_usr_trx_rcd as s1
left join
cmb_mch_typ as s2
on s1.mch_nm=s2.mch_nm
where
s1.usr_id=5201314520
and
s2.mch_typ in ('交通出行','休闲娱乐','咖啡奶茶')
group by
s2.mch_typ
,s1.mch_nm
),
flitered_t2 as
(
select
mch_typ
,mch_nm
,trx_cnt
,rnk
from
t2
where
rnk<=1
),
filtered_t1 as
(
select
mch_typ
,mch_nm
,trx_cnt
,rnk
from
t1
where
rnk<=1
)
select
mch_typ
,mch_nm
,trx_cnt
,rnk
from
t1
union all
select
mch_typ
,mch_nm
,trx_cnt
,rnk
from
t2
order by
mch_typ,
rnk,
mch_nm,
trx_cnt
with all_merchants_top_2 as (
select
'all' as mch_typ,
mch_nm,
count(*) as trx_cnt,
dense_rank() over (order by count(*) desc) as rnk
from
cmb_usr_trx_rcd
where
usr_id = 5201314520
group by
mch_nm
),
category_merchants_top_2 as (
select
m.mch_typ,
m.mch_nm,
count(t.trx_amt) as trx_cnt,
dense_rank() over (partition by m.mch_typ order by count(t.trx_amt) desc) as rnk
from
cmb_mch_typ m
join
cmb_usr_trx_rcd t on m.mch_nm = t.mch_nm
where
t.usr_id = 5201314520 and m.mch_typ in ('交通出行','休闲娱乐','咖啡奶茶')
group by
m.mch_typ, m.mch_nm
),
filtered_category_merchants_top_2 as (
select
mch_typ,
mch_nm,
trx_cnt,
rnk
from
category_merchants_top_2
where
rnk <= 1
),
filtered_all_merchants_top_2 as (
select
mch_typ,
mch_nm,
trx_cnt,
rnk
from
all_merchants_top_2
where
rnk <= 1
)
select
mch_typ,
mch_nm,
trx_cnt,
rnk
from
filtered_all_merchants_top_2
union all
select
mch_typ,
mch_nm,
trx_cnt,
rnk
from
filtered_category_merchants_top_2
order by
mch_typ,
rnk,
mch_nm,trx_cnt
select
t1.trx_mon,
COALESCE(t4.last_day, '1900-01-01') AS last_day,
COALESCE(t4.day_of_mon, 0) AS day_of_mon,
COALESCE(t4.trx_amt, 0) AS trx_amt,
COALESCE(t4.trx_cnt, 0) AS trx_cnt,
COALESCE(ROUND(t4.avg_day_amt, 2), 0) AS avg_day_amt,
COALESCE(ROUND(t4.avg_day_cnt, 2), 0) AS avg_day_cnt
from
(
select
distinct date_format(date_value,'%Y-%m') as trx_mon
from
date_table
where
date_format(date_value,'%Y-%m') between '2023-01' and '2024-06'
) as t1
left join
(
select
date_format(t2.trx_time,'%Y-%m') as trx_mon
,last_day(max(t2.trx_time)) as last_day
,day(last_day(max(t2.trx_time))) as day_of_mon
,sum(t2.trx_amt) as trx_amt
,count(*) as trx_cnt
,sum(t2.trx_amt)/ day(last_day(max(t2.trx_time))) as avg_day_amt
,count(*)/day(last_day(max(t2.trx_time))) as avg_day_cnt
from
cmb_usr_trx_rcd as t2
left join
cmb_mch_typ as t3
on t2.mch_nm=t3.mch_nm
where
date_format(t2.trx_time,'%Y-%m') between '2023-01' and '2024-06'
and
t2.usr_id=5201314520
and
t2.trx_amt>=288
and
hour(t2.trx_time) in (23,0,1,2)
and
(t3.mch_typ='休闲娱乐' or t3.mch_typ is null)
group by
date_format(t2.trx_time,'%Y-%m')
order by
1
) as t4
on t1.trx_mon=t4.trx_mon
order by
t1.trx_mon
select
t1.trx_mon,
COALESCE(t4.last_day, '1900-01-01') AS last_day,
COALESCE(t4.day_of_mon, 0) AS day_of_mon,
COALESCE(t4.trx_amt, 0) AS trx_amt,
COALESCE(t4.trx_cnt, 0) AS trx_cnt,
COALESCE(ROUND(t4.avg_day_amt, 2), 0) AS avg_day_amt,
COALESCE(ROUND(t4.avg_day_cnt, 2), 0) AS avg_day_cnt
from
(
select
date_format(date_value,'%Y-%m') as trx_mon
from
date_table
where
date_format(date_value,'%Y-%m') between '2023-01' and '2024-06'
) as t1
left join
(
select
date_format(t2.trx_time,'%Y-%m') as trx_mon
,last_day(max(t2.trx_time)) as last_day
,day(last_day(max(t2.trx_time))) as day_of_mon
,sum(t2.trx_amt) as trx_amt
,count(*) as trx_cnt
,sum(t2.trx_amt)/ day(last_day(max(t2.trx_time))) as avg_day_amt
,count(*)/day(last_day(max(t2.trx_time))) as avg_day_cnt
from
cmb_usr_trx_rcd as t2
left join
cmb_mch_typ as t3
on t2.mch_nm=t3.mch_nm
where
date_format(t2.trx_time,'%Y-%m') between '2023-01' and '2024-06'
and
t2.usr_id=5201314520
and
t2.trx_amt>=288
and
hour(t2.trx_time) in (23,0,1,2)
and
(t3.mch_typ='休闲娱乐' or t3.mch_typ is null)
group by
date_format(t2.trx_time,'%Y-%m')
order by
1
) as t4
on t1.trx_mon=t4.trx_mon
order by
t1.trx_mon