HQL 55 题【已完结】（4）

SELECT create_date,
     sum(IF(gender='男',total_amount,0)) total_amount_male,
       sum(IF(gender='女',total_amount,0)) total_amount_female
FROM
(
  SELECT oi.user_id,gender,total_amount,create_date
    FROM order_info oi 
    LEFT JOIN user_info ui ON oi.user_id=ui.user_id
)t1
GROUP BY create_date;

SELECT create_date,  
       SUM(CASE WHEN gender = '男' THEN total_amount ELSE 0 END) AS total_amount_male,  
       SUM(CASE WHEN gender = '女' THEN total_amount ELSE 0 END) AS total_amount_female  
FROM (  
    SELECT oi.user_id, ui.gender, oi.total_amount, oi.create_date  
    FROM order_info oi   
    LEFT JOIN user_info ui ON oi.user_id = ui.user_id  
) t1  
GROUP BY create_date;

SELECT user_id
FROM
(
  SELECT user_id,
     sum(IF(sku_id IN (1,2),1,0)) sum_num,
       MAX(CASE WHEN sku_id = 3 THEN 1 ELSE 0 END) AS has_sku_3
    FROM
    (
        SELECT DISTINCT od.sku_id,oi.user_id
        FROM order_detail od
        JOIN order_info oi ON od.order_id=oi.order_id
    )t1
    GROUP BY user_id
)t2
WHERE has_sku_3=0 AND sum_num=2;

SELECT
  create_date,
    cast(sum(total_amount) over(partition by ds rows between 2 preceding and current ROW) as decimal(16,2)) total_3d,
    cast(avg(total_amount) over(partition by ds rows between 2 preceding and current row) as decimal(16,2)) avg_3d
FROM
(
  SELECT
  t1.create_date,
    total_amount,
    date_sub(create_date,row_number() over(order by create_date)) ds
FROM
(
  SELECT
  create_date,
    sum(total_amount) total_amount
FROM order_info
group by create_date
  )t1
)t2;

SELECT coalesce(t1.create_date,t2.create_date) create_date,
     IF(t1.sku_num is null,0,t1.sku_num)-IF(t2.sku_num is null,0,t2.sku_num) diff
FROM
(
SELECT create_date,
       sum(sku_num) sku_num
FROM order_detail
WHERE sku_id='1'
GROUP BY create_date
)t1
FULL OUTER JOIN
(
SELECT create_date,
       sum(sku_num) sku_num
FROM order_detail
WHERE sku_id='2'
GROUP BY create_date
)t2
ON t1.create_date=t2.create_date;

SELECT user_id,
     order_id,
       create_date
FROM
(
    SELECT user_id,
           order_id,
           create_date,
           dense_rank() OVER(PARTITION BY user_id ORDER BY create_date DESC) rk
    FROM order_info
)t1
WHERE rk<=3;

SELECT user_id,
     max(diff) max_diff
FROM
(
  SELECT user_id,
     datediff(date_format(lead(login_ts,1,'2021-10-10') OVER(PARTITION BY user_id ORDER BY login_ts),'yyyy-MM-dd'),date_format(login_ts,'yyyy-MM-dd')) diff
    FROM user_login_detail
)t1
GROUP BY user_id;

SELECT DISTINCT user_id
FROM
(
  SELECT user_id,
     if(ip != next_ip AND login_ts <= next_login_ts,1,0) status
    FROM
    (
        SELECT user_id,
               ip_address ip,
               login_ts,
               lead(ip_address,1) OVER(PARTITION BY user_id ORDER BY login_ts) next_ip,
               lead(login_ts,1) OVER(PARTITION BY user_id ORDER BY login_ts) next_login_ts
        FROM user_login_detail
    )t1
)t2
WHERE t2.status=1;

SELECT sku_id,category_id
FROM
(
    SELECT  t1.sku_id,
            sum_num,
            si.category_id,
            row_number() OVER(PARTITION BY si.category_id ORDER BY sum_num DESC) rk
    FROM
    (
        SELECT sku_id,sum(sku_num) sum_num FROM order_detail GROUP BY sku_id
    )t1
    JOIN sku_info si ON si.sku_id=t1.sku_id
)t2 WHERE rk<=3;

SELECT  category_id,
    CAST(sum(price)/count(*) AS DECIMAL(16,2)) medprice
FROM
(
    SELECT  category_id,
            price,
            rk,
            max(rk) OVER(PARTITION BY category_id) max_rk
    FROM
    (
        SELECT  category_id,
            price,
                row_number() OVER(PARTITION BY category_id ORDER BY price) rk
        FROM sku_info
    )t1
)t2
WHERE rk IN (ceil((max_rk+1)/2),floor((max_rk+1)/2))
GROUP BY category_id;

select
    category_id,
    cast(avg(price) as DECIMAL(10,2)) medprice
from (
    select
        *,
        ceil(count / 2) mid,
        `if`(count % 2 = 0, 1, 0) flag
    from (
        select
            sku_id,
            category_id,
            price,
            row_number() over (partition by category_id order by price) rn,
            count(1) over (partition by category_id) count
        from sku_info
    )t
)t1 where rn = mid or rn = mid + flag
group by category_id;

SELECT DISTINCT sku_id
FROM
(
    SELECT  sku_id,
            create_date,
            row_number() OVER(PARTITION BY sku_id ORDER BY create_date) rk
    FROM 
    GROUP BY sku_id,create_date
    HAVING sum(price*sku_num)>100
)t1
GROUP BY sku_id,date_sub(create_date,rk)
HAVING count(*)>2;

SELECT  user_id,
    min(login_date) start_date,
        max(login_date) end_date
FROM
(
    SELECT  user_id,
            login_date,
            row_number() OVER(PARTITION BY user_id ORDER BY login_date) rk
    FROM
    (
        SELECT DISTINCT user_id,date_format(login_ts,'yyyy-MM-dd') login_date
        FROM user_login_detail
    )t1
)t2
GROUP BY user_id,date_sub(login_date,rk)
HAVING count(*)>1;

SELECT  sku_id,
    min(create_date) start_date,
        max(create_date) end_date
FROM
(
    SELECT  sku_id,
            create_date,
            row_number() OVER(PARTITION BY sku_id ORDER BY create_date) rk
    FROM 
    (
        SELECT DISTINCT sku_id,create_date FROM order_detail
    )t1
)t2
GROUP BY sku_id,date_sub(create_date,rk)
HAVING count(*)>0;