高级SQL查询技巧——利用SQL改善和增强你的数据

select t1.date
  , sum(t1.widgets_sold) as total_widgets_sold
from db2.widget_order_history t1
where t1.date between ‘2021–04–05’ and ‘2021–05–01’
group by t1.date

select t1.date
   , sum(t1.widgets_sold) as total_widgets_sold
from db2.widget_order_history t1
where (t1.date between ‘2021–04–05’ and ‘2021–05–01’)
   and t1.store = 1234
group by t1.date

select t1.date
   , sum(t1.widgets_sold) as total_widgets_sold
from db2.widget_order_history t1
inner join (
   select store
   from db2.store_data
   where state = ‘NY’
) t2
on t1.store = t2.store
where t1.date between ‘2021–04–05’ and ‘2021–05–01’
group by t1.date

select a.date
, a.total_widgets_sold
   , sum(
   case when (b.date between a.date-7 and a.date-1) 
   then b.total_widgets_sold 
   else 0 
   end)/7 as seven_day_avg
from (
   select date
      , sum(widgets_sold) as total_widgets_sold
   from db2.widget_order_history
   where date between ‘2021–04–25’ and ‘2021–05–01’
   group by date
) a
left join (
   select date
      , sum(widgets_sold) as total_widgets_sold
   from db2.widget_order_history
   where date between ‘2021–04–05’ and ‘2021–05–01’
   group by date
) b
on a.date = b.date
group by a.date
   , a.total_widgets_sold
order by a.date

select a.date
   , a.total_widgets_sold
   , b.total_widgets_sold as prev_wkday_sales
from (
   select date
      , sum(widgets_sold) as total_widgets_sold
   from db2.widget_order_history
   where date between ‘2021–04–25’ and ‘2021–05–01’
   group by date
) a
left join (
   select date
      , sum(widgets_sold) as total_widgets_sold
   from db2.widget_order_history
   where date between ‘2021–04–04’ and ‘2021–05–01’
   group by date
) b
on a.date -7 = b.date

## Example of Nested Logic in R
if(shoppers$sales<=0){ print("Error: Negative/No Sales")}
else if(shoppers&sales<=100){ print("Shopper has below-average sales.")}
else if(shoppers&sales<=200){ 
   if(shopper$age<41){print("Shopper has average sales and is Gen Z/Millennial.")}
   else{
 if(shopper$state=='NJ'){print("Shopper has average sales, is Gen X/Boomer/Greatest Gen., and lives in New Jersey.")}
 else if(shopper$state=='NY'){print("Shopper has average sales, is Gen X/Boomer/Greatest Gen., and lives in New York.")
 else(shopper$state=='CT'){print("Shopper has average sales, is Gen X/Boomer/Greatest Gen., and lives in Connecticut.")}
   }
}
else{print("Shopper has above-average sales.")}
*Example of nested logic in SQL. No need to actually nest statements!;
, case when sales < 0
 then 'Error: Negative/No Sales.'
when sales <=100
 then 'Shopper has below-average sales.'
when sales <=200 and age <41
 then 'Shopper has average sales and is Gen Z/Millennial.'
when sales <=200 and state = 'NJ'
 then 'Shopper has average sales, is Gen X/Boomer/Greatest Gen., and lives in New Jersey.'
when sales <=200 and state = 'NY'
 then 'Shopper has average sales, is Gen X/Boomer/Greatest Gen., and lives in New York.'
when sales <=200 and state = 'CT'
 then 'Shopper has average sales, is Gen X/Boomer/Greatest Gen., and lives in Connecticut.'
else 'Shopper has above-average sales.'
end as shopper_classification