回首页

1 什么是空间维度

比如营业额,以不同级别的空间,可以分为 区营业额市营业额省营业额。可以省市区的数据,称之为-空间维度的数据分析

2 怎么使用

比如有省市区三个表,和一个订单表

Province

City

Region

订单 Order

这里数据分两块,一个是区域,一个是订单,区域和订单有着某种关系。

先准备区域基础数据

select * from city inner join region on region.city_id=city.id inner join  province on city.province_id=province.id

Order里必有一个与区域有关的字段,可以是某一个店铺的id,也可以是region_id。这里假设是region_id

region_id是空间里维度最低的,先准备低空间的基础数据

select region_id ,sum(pay_price) as total_price from order groupby region_id

现在有了区域的基础数据和订单的基础数据,把这 两块数据,聚合在一起,就有了区营业额,市营业额省营业额

区营业额 即上方的低空间维度的数据

select region_id ,sum(pay_price) as total_price from order groupby region_id

市营业额区营业额中相同市级营业额的聚合。

select location.province_id,location.city_id, sum(location.total_price) as total_price  from (select region_id ,sum(pay_price) as total_price from order groupby region_id) as order inner join (select * from city inner join region on region.city_id=city.id inner join  province on city.province_id=province.id) as location order.region_id=location.region_id groupby location.city_id

上面的查询为什么可以带着 location.province_id ,因为相同city_idprovince_id 也是一样的 ,如果不一样则会报

[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

省营业额区营业额中相同省级营业额的聚合。

select location.province_id, sum(location.total_price) as total_price  from (select region_id ,sum(pay_price) as total_price from order groupby region_id) as order inner join (select * from city inner join region on region.city_id=city.id inner join  province on city.province_id=province.id) as location order.region_id=location.region_id groupby location.province_id

3 Laravel 怎么优雅使用

1先准备区域基础数据

use DB;
$location=DB::table('city')->join('province','province.id','=','city.province_id')->join('region','city.id','=','region.city_id');

2订单基础数据

$order=DB::table('order')->select(DB::raw("region_id ,sum(pay_price) as total_price"))->groupBy('region_id')

3 联合子查询

由于不空间维度的查询语句是不一样的,因此要根据传参的不同,sql语句也是不一样的。联合起来可以把空间的信息传递给前端,便于展示

$data=[];
if(request()->location_type=='region'){//区级维度
   $data= DB::query()->fromSub($order,'order')->joinSub($location,'loction',function($join){
        $join->on('order.region_id','=','location.region_id');
    })->get();
}elseif(request()->location_type=='city'){//市级维度
    $raw="location.province_id,location.city_id, sum(location.total_price) as total_price";
     $data= DB::query()->select(DB::raw($raw))->fromSub($order,'order')->joinSub($location,'loction',function($join){
        $join->on('order.region_id','=','location.region_id');
    })->groupBy('city_id')->get();
}elseif(request()->location_type=='province'){//省级维度
    $raw="location.province_id sum(location.total_price) as total_price";
     $data= DB::query()->select(DB::raw($raw))->fromSub($order,'order')->joinSub($location,'loction',function($join){
        $join->on('order.region_id','=','location.region_id');
    })->groupBy('province_id')->get();
}
return $data;