正规军之路-part1-SQL数据分析

2024-01-05 17:18:22

进入系统的正规学习后才发现自己对python的应用实在是少得可怜.

SQL实现对数据库的访问及分析,至今为止已经进入了4课:

1 - 数据分析概论:一个很大的范围里面确定一件很具体的事情;

2 - SQL对数据进行查询:select列名from表名where筛选条件order by排序limit数量;

3 - 用SQL抽样统计:count,sum,max,min,avg+group by聚合函数的使用及分析;

select a.groupid,a.total_kill,
count(a.total_kill) as frequency
from
(select groupid,matchid,
sum(kills) as total_kill
from juediqs
group by groupid) a
group by total_kill
order by frequency asc limit 100;

　　

4 - 用SQL关联,转置:

列名相等，用union all将数据首位相接;select 字段 from a表 union all select 字段 from b表

create table ds_indian_restarants_price
select f1 as item_name, f2 as product_price, ‘res_1‘ as brand_tag
from restaurant1
union ALL
select f1 as item_name, f2 as product_price, ‘res_2‘ as brand_tag
from restaurant2;

　　

取A，B表关联，关键字段进行拼接：select a.* b.字段 from 表名 a join 表名 b on a.字段=b.字段 and a.字段2=b.字段2

create table total_indian_restaurant_order_price
SELECT a.`order number` as order_number,
a.`order date` as order_date,
a.`item name` as item_name,
a.quantity,a.brand_tag,
a.`total products` as products_number, 
b.product_price
from ds_indian_restaurants_orders a JOIN
ds_indian_restarants_price b ON
a.`item name` = b.item_name and a.brand_tag=b.brand_tag;

　　

inner join实现对AB的交集
left/right join（where B/A.key is null)实现对A/B-AB的交集

正规军之路-part1-SQL数据分析