可能不正確

1.

用戶訂單表ord，字段user_id,ord_id,goods_id,goods_num,ord_amt,create_time

求最近30天每件商品的平均售價

select good_id,sum(ord_amt)/sum(good_num)
from ord
where datediff(now(),date(create_time))<=30
group by goods_id

2.

用戶瀏覽網(wǎng)站頁面流水表pv_log，字段pv_id,user_id,create_time

網(wǎng)站用戶維表dim_user，字段user_id,age,create_time

求瀏覽不同頁面的用戶對應(yīng)的平均年齡

select pv_id,sum(dim_user.age)/count(dim_user.age)
from pv_log left join dim_user on pv_log.user_id=dim_user.user_id
group by pv_log.pv_id

3.

用戶訂單表tbl_ordr，字段user_id,ordr_id,ord_goods,ordr_time

用戶商品點擊明細(xì)表clk,字段clk_id,user_id,clk_time,clk_goods

假設(shè)僅有一天數(shù)據(jù)，點擊和下單必須是同一用戶同一商品，多次點擊然后下單的話算最后一次點擊。輸出用戶點擊后下單的記錄，clk_id，ordr_id

select tbl_ordr.user_id,tbl_ordr.ord_goods
from tbl_ordr left join clk on tbl_ordr.user_id=clk.user_id and tbl_ordr.ord_goods=clk.clk_goods
where tbl_ordr.clk_time>=clk.clk_time
group by (tbl_ordr.user_id,tbl_ordr.ord_goods)