Q1:求商品的销售数量/实收金额/购买用户数
select product_code,
count(product_code),
sum(income),
count(distinct user_id)
from fact_order
where ptdate = '2020-07-08'
group by product_code
Q2:次日留存
select d.a_dt,
count(distinct case when d.day_diff = 1 then d.user_id else null end) as 次日留存数
from
(
select *,timestampdiff(day,a_dt,b_dt) as day_diff
from
(
select a.user_id,
a.ptdate as a_dt,
b.ptdate as b_dt
from (
select user_id,ptdate
from fact_order
where ptdate>='2020-07-08' and ptdate<='2020-07-12'
)as a
left join (
select user_id, ptdate
from fact_order
where ptdate>='2020-07-08' and ptdate<='2020-07-12'
) as b
on a.user_id = b.user_id
) as c
) as d
group by d.a_dt
Q3：分段汇总
select price_range,count(price_range)
from
(
select
(case when income>=0 and income<30 then '0~30'
case when income>=30 and income<60 then '30~60'
case when income>=60 and income<90 then '60~90'
else '90以上' END) as price_range
)
from fact_order
) a
group by price_range,count
Q4：取前三，逗号分隔
select user_id, group_concat(category_name)
from(
select a.user_id,b.category_name,sum(a.income)
row_number() over(partition by a.user_id order by sum(a.income) desc) as incom_rank
from fact_order as a
left join dim_product as b
on a.product_code = b.product_code
group by user_id,category_name
where row_number() over(partition by a.user_id order by sum(a.income) desc)<=3
) c
group by c.user_id
不合并，分成3列，不用group_contact:
SELECT shopname,
MAX(CASE WHEN sumprice_rank = 1 THEN t.goodsname ELSE '' END) AS goodsname1,
MAX(CASE WHEN sumprice_rank = 2 THEN t.goodsname ELSE '' END) AS goodsname2,
MAX(CASE WHEN sumprice_rank = 3 THEN t.goodsname ELSE '' END) AS goodsname3
FROM
(SELECT shop.shopname, goods.goodsname, a.sumprice, a.sumprice_rank FROM
(SELECT shopid,
goodsid,
SUM(salenum * price) AS sumprice,
ROW_NUMBER() OVER (PARTITION BY shopid ORDER BY SUM(salenum * price) DESC) AS sumprice_rank
FROM sales
WHERE DATE_SUB(CURDATE(), INTERVAL 3 MONTH) <= date(orderdate)
GROUP BY shopid, goodsid) a
LEFT JOIN shop ON a.shopid = shop.shopid
LEFT JOIN goods ON a.goodsid = goods.goodsid
WHERE a.sumprice_rank <= 3
ORDER BY shopname, sumprice_rank) t
GROUP BY shopname;
其他：
- BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
1、BETWEEN value1 AND value2，左闭右闭。
2、显示介于 "Adams"（包括）和 "Carter"（不包括）之间的人。（BETWEEN 'Adams' AND 'Carter'）
3、使用上面的例子显示范围之外的人。NOT BETWEEN 'Adams' AND 'Carter'
- timestampdiff()
TIMESTAMPDIFF(unit,begin,end);
MICROSECOND 微秒
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
WEEK 周
MONTH 月份
QUARTER
YEAR 年份
SELECT TIMESTAMPDIFF(MONTH, ‘2018-01-01’, ‘2018-06-01’) result；结果：5；
GROUP_CONCAT
group_concat([DISTINCT] 要连接的字段 [ORDER BY ASC/DESC 排序字段] [separator ‘分隔符’]) 默认逗号分隔
示例：
select store_id,group_concat(name order by store_id desc Separator ‘*’),sum(num) from product group by store_id;
– 创建表结构
DROP TABLE IF EXISTS exe;
CREATE TABLE exe (
id int(3) NOT NULL,
type int(3) default NULL,
name varchar(10) default NULL,
other int(3) default NULL,
text int(255) default NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– 插入测试数据
INSERT INTO exe VALUES (‘1’, ‘1’, ‘分拼’, ‘2’, ‘1’);
INSERT INTO exe VALUES (‘2’, ‘1’, ‘四维’, ‘3’, ‘2’);
INSERT INTO exe VALUES (‘3’, ‘2’, ‘总评’, ‘1’, ‘4’);
INSERT INTO exe VALUES (‘4’, ‘3’, ‘季度’, ‘5’, ‘3’);
– group_concat和group by的使用
– 默认逗号连接
select t.type,group_concat(t.name) “result” from exe t group by t.type;
– separator指定连接符
select t.type,group_concat(t.name separator ‘;’) “result” from exe t group by t.type;
– 排序连接
select t.type,group_concat(t.name order by t.other desc) “result” from exe t group by t.type;
-- 在字符串有重复的值，把重复的值过滤
group_concat(distinct t.name)
注意：
group_concat字符串的长度字节超过1024 则会被截断，可以通过命令 "show variables like 'group_concat_max_len' " 来查看group_concat 默认的长度。
设置group_concat长度的操作方法：
(1)在MySQL配置文件中加入:
group_concat_max_len = 102400
(2)更简单的操作方法，执行SQL语句：
SET GLOBAL group_concat_max_len = 102400;
SET SESSION group_concat_max_len = 102400;
注意：该方法缺点是重启服务后设置失效。