mysql实战练习

-- SELECT count(*) FROM fct_sales_item;
-- 
-- SELECT    * FROM    fct_sales_item
-- WHERE id<10;
-- 
-- SELECT DATE_FORMAT(dimDateID,'%Y-%m-%d') as 'date'
-- FROM dim_date
-- ORDER BY dimDateID DESC
-- LIMIT    10;
-- 
-- SELECT    now();
-- 

-- 第四章-常用函数作业
-- 1.统计 每天 购物的 顾客数,销售金额,订单量
SELECT
    DATE_FORMAT( fct_sales.dimDateID, '%Y-%m-%d' ) AS everyday,
    COUNT( DISTINCT fct_sales.dimMemberID ) AS '顾客数',
    sum( fct_sales.AMT ) AS '销售金额',
    sum( fct_sales.QTY ) AS '订单量' 
FROM
    fct_sales 
GROUP BY
    fct_sales.dimDateID -- 以天分组计算
    
ORDER BY
    fct_sales.dimDateID DESC;
    

-- 2.统计 每周 顾客数,销售金额,订单量
SELECT WEEK
    ( fct_sales.dimDateID, 1 ) AS '一年第几周',
    COUNT( fct_sales.dimMemberID ) AS '顾客数',
    sum( fct_sales.AMT ) AS '销售金额',
    sum( fct_sales.QTY ) AS '订单量' 
FROM
    dw.fct_sales 
GROUP BY
    WEEK ( fct_sales.dimDateID, 1 );  -- 以周分组计算

-- 3. 统计各个时间段的 顾客数,销售金额, 订单量
SELECT HOUR
    ( fct_sales.dimDateID ) AS '每小时hour',
    WEEKDAY( fct_sales.dimDateID ) AS '星期几',
    COUNT( fct_sales.dimMemberID ) AS '顾客数',
    sum( fct_sales.AMT ) AS '销售金额',
    sum( fct_sales.QTY ) AS '订单量' 
FROM
    dw.fct_sales 
GROUP BY
    HOUR ( fct_sales.dimDateID ),
    weekday ( fct_sales.dimDateID );


-- 4. 每天交易金额、订单数量、顾客数、平均客单价；
--    方法一: dayofyear函数 
SELECT
    DAYOFYEAR( fct_sales.dimDateID ) AS '一年第几天',
    sum( fct_sales.AMT ) AS '交易金额',
    sum( fct_sales.QTY ) AS '订单数',
    count( fct_sales.dimDateID ) AS '顾客数',
    sum( fct_sales.AMT ) / count( fct_sales.dimDateID ) AS '平均客单价' 
FROM
    fct_sales 
GROUP BY
    DAYOFYEAR( fct_sales.dimDateID );--  方法二: date_format函数
SELECT
    DATE_FORMAT( fct_sales.dimDateID, '%j' ) AS '一年第几天',
    sum( fct_sales.AMT ) AS '交易金额',
    sum( fct_sales.QTY ) AS '订单数',
    count( fct_sales.dimDateID ) AS '顾客数',
    sum( fct_sales.AMT ) / count( fct_sales.dimDateID ) AS '平均客单价' 
FROM
    fct_sales 
GROUP BY
    DATE_FORMAT( fct_sales.dimDateID, '%j' );
    
    
-- 5. 查询某周 金额最大、最小的 订单号 及对应的顾客；【自行选择】
-- 这里选择一年中23周
SELECT
    fct_sales.salesID,
    fct_sales.dimMemberID 
FROM
    fct_sales 
WHERE
    fct_sales.AMT = ( SELECT max( fct_sales.AMT ) FROM fct_sales WHERE WEEK ( fct_sales.dimDateID ) = 23 ) 
    OR fct_sales.AMT = ( SELECT min( fct_sales.AMT ) FROM fct_sales WHERE WEEK ( fct_sales.dimDateID ) = 23 );


-- 6. 统计XX日期到XX日期会员购买金额，去掉金额大于1000会员。【自行选择时间范围】
-- 统计 20170611到20170730 会员买$  去掉 $>1000
SELECT
    fct_sales.dimMemberID AS '顾客ID',
    SUM( fct_sales.AMT ) AS '购买金额' 
FROM
    fct_sales 
WHERE
    ( fct_sales.dimDateID BETWEEN 20170611 AND 20170730 ) 
GROUP BY
    fct_sales.dimMemberID 
HAVING
    sum( fct_sales.AMT ) <= 1000;

-- 7. 计算平均客单价，平均每个顾客购买金额，计算客户平均购买次数
--  客单价=销售总额（除去打折等优惠之后的算下来的钱）÷ 顾客总数
SELECT
    avg( fct_sales.AMT ) AS '客单价',
    sum( fct_sales.AMT ) / count( DISTINCT fct_sales.dimMemberID ) AS '平均每位顾客购买金额',
    COUNT( fct_sales.salesID ) / COUNT( DISTINCT fct_sales.salesID ) AS '客户平均购买次数'
FROM
    fct_sales;

-- 8. 统计订单明细表，把商品价格进3等分，统计每个区间的商品数
-- 9. 每天订单金额分布（按最大，最小分成3个等分区间）
SELECT
    ( max( AMT ) - min( AMT ) ) / 3 AS cut_amt,
    min( AMT ) AS min_amt,
    max( AMT ) AS max_amt 
FROM
    fct_sales;
SELECT
    (
    CASE
            
            WHEN fct_sales.AMT <= ( 0.17000 + 8781.943333333 ) THEN
            '[min_amt,cut_amt]' 
            WHEN fct_sales.AMT <= ( 0.17000 + 8781.943333333 * 2 ) AND ( AMT > ( 0.17000 + 8781.943333333 )) THEN
                '(min_amt+cut_amt,min_amt+cut_amt*2]' 
            WHEN fct_sales.AMT <= 26346.00000 THEN
                '(min_amt+cut_amt*2,min_amt + cut_amt*3]' 
            END 
    ) AS qj,
    count( amt ) 
    FROM
        fct_sales

GROUP BY
    (
    CASE
            
            WHEN fct_sales.AMT <= ( 0.17000 + 8781.943333333 ) THEN
            '[min_amt,cut_amt]' 
            WHEN fct_sales.AMT <= ( 0.17000 + 8781.943333333 * 2 ) AND ( AMT > ( 0.17000 + 8781.943333333 )) THEN
                '(min_amt+cut_amt,min_amt+cut_amt*2]' 
            WHEN fct_sales.AMT <= 26346.00000 THEN
                '(min_amt+cut_amt*2,min_amt + cut_amt*3]' 
            END 
    );

-- 第五章 子查询,实战练习题
-- 1. 计算2017年7月份2号每个会员购买金额, 以及每个会员购买金额占总金额的比
-- 思路:
--     先对会员进行分组, 再根据会员组,也就是每位会员购买金额进行sum, 限制条件:日期
--     通过子查询,统计在条件日期内, 全体会员购买总金额,相除计算比率
--     比率格式: 利用concat 和 round 函数 进行数据格式处理
SELECT dw.fct_sales.dimMemberID,
             sum(dw.fct_sales.AMT) AS member_money,
             (SELECT sum(AMT) from dw.fct_sales WHERE dw.fct_sales.dimDateID = 
             '20170702' AND    dw.fct_sales.dimMemberID <>0) AS total_money,
             concat(round((sum(dw.fct_sales.AMT)/(SELECT sum(AMT) from dw.fct_sales WHERE dw.fct_sales.dimDateID = 
             '20170702' AND    dw.fct_sales.dimMemberID <>0))*100,4),'%') AS member_total_rave_2
FROM fct_sales
WHERE dw.fct_sales.dimDateID = '20170702' AND    dw.fct_sales.dimMemberID <>0
GROUP by dw.fct_sales.dimMemberID
ORDER BY SUM(AMT) DESC;

-- 2. 2017年07月2号 对每位会员累计购买金额进行分段
--  思路: 
--      先进行会员分组,限制条件: 日期
--      对每一位会员sum购买金额
--      把上面查询的 作为表即子查询,生成temp临时表,用来from(除了它,其他都只能是一个字段)
--      注意: 用临时表时需用临时表别名alis, 相当于数据库中有一个临时表temp
--      再进行分段使用 case 再排序order by
SELECT  dw.temp.dimMemberID,
              member_money,
                CASE  
                            WHEN member_money < 100 THEN 'D'
                            WHEN member_money < 500 AND member_money >= 100 THEN 'C'
                            WHEN member_money < 1000 AND member_money >= 500 THEN 'B'
                            WHEN member_money >= 1000 THEN 'A'
                END AS type_money
from 
        (    SELECT dw.fct_sales.dimMemberID,
                            sum(dw.fct_sales.amt) as member_money
            FROM fct_sales
            where dw.fct_sales.dimDateID='20170702' AND dimMemberID <>0
            GROUP BY dw.fct_sales.dimMemberID
        ) AS temp
ORDER BY type_money;


-- 3. 2017年07月2号 统计累计购买金额在100到200的会员,寻找这批会员的消费记录
--  思路:
--     把累计金额100到200的会员作为筛选表 在这批会员中寻找消费记录
--     先筛选 : 时间条件 再在会员分组中筛选 金额范围
--     还需在外查询中 设置: 时间条件, 因为in后面只是会员ID ,会员也可能在 其他时间段有记录
--      IN 后面必须只有一个字段

--         SELECT dw.fct_sales.dimMemberID
--         FROM    fct_sales
--         WHERE dw.fct_sales.dimDateID = '20170702'
--         GROUP BY    dw.fct_sales.dimMemberID
--         HAVING    SUM(dw.fct_sales.AMT) >= 100 AND SUM(dw.fct_sales.AMT) <= 200 AND dw.fct_sales.dimMemberID <> 0

SELECT *
FROM dw.fct_sales
WHERE dw.fct_sales.dimDateID = '20170702' AND dw.fct_sales.dimMemberID<>0 AND dw.fct_sales.dimMemberID IN
(        SELECT dw.fct_sales.dimMemberID
        FROM    fct_sales
        WHERE dw.fct_sales.dimDateID = '20170702'
        GROUP BY    dw.fct_sales.dimMemberID
        HAVING    SUM(dw.fct_sales.AMT) >= 100 AND SUM(dw.fct_sales.AMT) <= 200 AND dw.fct_sales.dimMemberID <> 0
)
ORDER BY dw.fct_sales.AMT DESC;

-- #1 计算 7 月份某周每个会员订单数，以及每个会员订单占整体比
-- 思路:
--   用分组对会员进行分组, 统计每个会员订单数, 在通过子查询统计会员总订单数
--   挑选条件:  日期, 某周
SELECT     dw.fct_sales.dimMemberID,
                COUNT(salesNo) AS sales_num,
                CONCAT((count(salesNo)/(SELECT COUNT(salesNo) FROM dw.fct_sales WHERE (dw.fct_sales.dimDateID BETWEEN '20170703' AND '20170709') AND dimMemberID <>0 ))*100,'%') AS rate
FROM    dw.fct_sales
WHERE (dw.fct_sales.dimDateID BETWEEN '20170703' AND '20170709') AND dimMemberID <>0
GROUP BY dw.fct_sales.dimMemberID
ORDER BY rate DESC;

-- #2 对 7 月份某周每位会员累计购买金额进行分段统计，并统计每段的会员数
-- 思路:
--    子查询: 安照购买金额统计,以会员为分组  筛选条件: 时间
--     将子查询作为 临时表,再统计每段会员数
-- SELECT    dw.fct_sales.dimMemberID,
--                 sum(AMT)
-- FROM dw.fct_sales
-- WHERE  (dw.fct_sales.dimDateID BETWEEN '20170703' AND '20170709') AND dimMemberID <>0
-- GROUP BY dimMemberID
-- 
SELECT 
        (CASE 
            WHEN temp.sum_amt <= 300 THEN  '0-300'
            WHEN temp.sum_amt <= 800 AND temp.sum_amt >300 THEN '300-800'
            WHEN temp.sum_amt >800 THEN '800-'
        END    ) AS cut_type,
        COUNT(DISTINCT dimMemberID) AS memberID_number 
FROM    
    (SELECT    dw.fct_sales.dimMemberID,
                    sum(AMT) as sum_amt
    FROM dw.fct_sales
    WHERE  (dw.fct_sales.dimDateID BETWEEN '20170703' AND '20170709') AND dimMemberID <>0
    GROUP BY dimMemberID
    ) as temp 
GROUP BY CASE 
            WHEN temp.sum_amt <= 300 THEN  '0-300'
            WHEN temp.sum_amt <= 800 AND temp.sum_amt >300 THEN '300-800'
            WHEN temp.sum_amt >800 THEN '800-'
        END    ;

-- #3 查找 7 月份某周订单金额最大的会员，这个会员的所有消费记录
-- 思路:
--         

select *
from dw.fct_sales
where dimMemberID in
(        select distinct dimMemberID
        from dw.fct_sales
        where AMT in
                            (select max(AMT)
                            from dw.fct_sales
                            where dimDateID between '20170703' and '20170709'
                            and dimMemberID<>0
                            )
) 
-- AND    (dimDateID between '20170703' and '20170709' and dimMemberID<>0);

-- 月报：统计 201707 月内每天及本月累计销售金额、订单量、会员数、订单占比、会员渗透率
/* 思路:
select  天, 销售金额, 订单量, 会员数, 天订单/总订单(子查询), 天会员数(去重)/月总会员数(去重
FROM    交易事实表
WHERE   月 
GROUP BY 天
HAVING    无
ORDER BY 天     -- 只能在最后使用!!!!
UNION 
SELECT    月, ...., '100%', ''100%'
FROM    交易表
WHERE     月
GROUP BY 月
HAVING 无
ORDER BY 月
*/
-- 疑问 : SELECT count(DISTINCT dimMemberID) FROM    fct_sales  WHERE dimMemberID<>0

SELECT    dw.fct_sales.dimDateID ,
                sum(dw.fct_sales.AMT)    AS    sales_money ,
                COUNT(dw.fct_sales.salesID) AS    sales_number,
                COUNT(DISTINCT dw.fct_sales.dimMemberID) AS member_number ,
                count(dw.fct_sales.salesID)/(SELECT count(dw.fct_sales.salesID) FROM    dw.fct_sales WHERE    dw.fct_sales.dimDateID BETWEEN '20170701' AND '20170731' ) AS order_rate,
                COUNT(DISTINCT dw.fct_sales.dimMemberID)/(SELECT count(dw.fct_sales.dimMemberID) FROM    dw.fct_sales WHERE    dw.fct_sales.dimDateID BETWEEN '20170701' AND '20170731' AND dw.fct_sales.dimMemberID <> 0) AS member_rate
FROM    dw.fct_sales
WHERE    dw.fct_sales.dimDateID BETWEEN '20170701' AND '20170731'
GROUP BY    dimDateID
UNION    
SELECT    MONTH(dimDateID),
                sum(dw.fct_sales.AMT)    AS    sales_money ,
                COUNT(dw.fct_sales.salesID) AS    sales_number,
                COUNT(DISTINCT dw.fct_sales.dimMemberID) AS member_number ,
                count(dw.fct_sales.salesID)/(SELECT count(dw.fct_sales.salesID) FROM    dw.fct_sales WHERE    dw.fct_sales.dimDateID BETWEEN '20170701' AND '20170731' ) AS order_rate,
                COUNT(DISTINCT dw.fct_sales.dimMemberID)/(SELECT count(dw.fct_sales.dimMemberID) FROM    dw.fct_sales WHERE    dw.fct_sales.dimDateID BETWEEN '20170701' AND '20170731' AND dw.fct_sales.dimMemberID <> 0 ) AS member_rate
FROM    dw.fct_sales
WHERE    dw.fct_sales.dimDateID BETWEEN '20170701' AND '20170731'
GROUP BY    MONTH(dimDateID) 
ORDER BY     dimDateID;     -- 只能在最后使用!!!!

-- - 对星期进行排序 
select date_format(dimDateID,'%W') as week_day
      ,sum(AMT) as sales_money
      ,count(distinct salesID) as order_number
      ,count(distinct dimMemberID) as member_number
      ,count(distinct salesID)/(select count(distinct salesID) from dw.fct_sales
                                where dimDateID between '20170703' and '20170710') as order_rate
from dw.fct_sales
where dimDateID between '20170703' and '20170709'
group by date_format(dimDateID,'%W')
-- order by date_format(dimDateID,'%W')
union 
select date_format(dimDateID,'%W') as week_day
      ,sum(AMT) as sales_money
      ,count(distinct salesID) as order_number
      ,count(distinct dimMemberID) as member_number
      ,count(distinct salesID)/(select count(distinct salesID) from dw.fct_sales
                                where dimDateID between '20170703' and '20170710') as order_rate
from dw.fct_sales
where dimDateID between '20170703' and '20170709'
group by date_format(dimDateID,'%W');


-- - 要排序 , 用别名排序

select temp.*
from (
select date_format(dimDateID,'%W') as week_day
      ,sum(AMT) as sales_money
      ,count(distinct salesID) as order_number
      ,count(distinct dimMemberID) as member_number
      ,count(distinct salesID)/(select count(distinct salesID) from dw.fct_sales
                                where dimDateID between '20170703' and '20170710') as order_rate
from dw.fct_sales
where dimDateID between '20170703' and '20170709'
group by date_format(dimDateID,'%W')
-- order by date_format(dimDateID,'%W')
union 
select date_format(dimDateID,'%W') as week_day
      ,sum(AMT) as sales_money
      ,count(distinct salesID) as order_number
      ,count(distinct dimMemberID) as member_number
      ,count(distinct salesID)/(select count(distinct salesID) from dw.fct_sales
                                where dimDateID between '20170703' and '20170710') as order_rate
from dw.fct_sales
where dimDateID between '20170703' and '20170709'
group by date_format(dimDateID,'%W')) temp
order by week_day