Solr查询与sql对比转化

查询对比：

-------------------------------------------------------------------------------------------------------------------------------------------

1、条件组合查询

SQL查询语句：

SELECT log_id,start_time,end_time,prov_id,city_id,area_id,idt_id,cnt,net_type

FROM v_i_event

WHERE prov_id = 1 AND net_type = 1 AND area_id = 10304 AND time_type = 1 AND time_id >= 20130801 AND time_id <= 20130815

ORDER BY log_id LIMIT 10;

Solr查询URL：

http://192.x.x.x:port/xxx/core1/select?q=*:*&fl=log_id,start_time,end_time,prov_id,city_id,area_id,idt_id,cnt,net_type&fq=prov_id:1 AND net_type:1 AND area_id:10304 AND time_type:1 AND time_id:[20130801 TO 20130815]&sort=log_id asc&start=0&rows=10

2、单个字段分组统计

SELECT prov_id, SUM(cnt) AS sum_cnt, AVG(cnt) AS avg_cnt, MAX(cnt) AS max_cnt, MIN(cnt) AS min_cnt, COUNT(cnt) AS count_cnt

FROM v_i_event

GROUP BY prov_id;

Solr查询URL：

http://192.x.x.x:port/xxx/core1/select?q=*:*&stats=true&stats.field=cnt&rows=0&indent=true

3、IN条件查询

SQL查询语句：

SELECT log_id,start_time,end_time,prov_id,city_id,area_id,idt_id,cnt,net_typ

FROM v_i_event

WHERE prov_id = 1 AND net_type = 1 AND city_id IN(106,103) AND idt_id IN(12011,5004,6051,6056,8002) AND time_type = 1 AND time_id >= 20130801 AND time_id <= 20130815

ORDER BY log_id, start_time DESC LIMIT 10;

Solr查询URL：

http://192.x.x.x:port/xxx/core1/select?q=*:*&fl=log_id,start_time,end_time,prov_id,city_id,area_id,idt_id, cnt,net_type&fq=prov_id:1 AND net_type:1 AND (city_id:106 OR city_id:103) AND (idt_id:12011 OR idt_id:5004 OR idt_id:6051 OR idt_id:6056 OR idt_id:8002) AND time_type:1 AND time_id:[20130801 TO 20130815]&sort=log_id asc ,start_time desc&start=0&rows=10

或

http://192.x.x.x:port/xxx/core1/select?q=*:*&fl=log_id,start_time,end_time,prov_id,city_id,area_id,idt_id, cnt ,net_type&fq=prov_id:1&fq=net_type:1&fq=(city_id:106 OR city_id:103)&fq=(idt_id:12011 OR idt_id:5004 OR idt_id:6051 OR idt_id:6056 OR idt_id:8002)&fq=time_type:1&fq=time_id:[20130801 TO 20130815]&sort=log_id asc,start_time desc&start=0&rows=10

4、开区间范围条件查询

SQL查询语句：

SELECT log_id,start_time,end_time,prov_id,city_id,area_id,idt_id,cnt,net_type

FROM v_i_event

WHERE net_type = 1 AND idt_id IN(12011,5004,6051,6056,8002) AND time_type = 1 AND start_time >= 1373598465 AND end_time < 1374055254

ORDER BY log_id, start_time, idt_id DESC LIMIT 30;

Solr查询URL：

http://192.x.x.x:port/xxx/core1/select?q=*:*&fl=log_id,start_time,end_time,prov_id,city_id,area_id,idt_id,cnt,net_type&fq=net_type:1 AND (idt_id:12011 OR idt_id:5004 OR idt_id:6051 OR idt_id:6056 OR idt_id:8002) AND time_type:1 AND start_time:[1373598465 TO 1374055254]&fq =-start_time:1374055254&sort=log_id asc,start_time asc,idt_id desc&start=0&rows=30

或

http://192.x.x.x:port/xxx/core1/select?q=*:*&fl=log_id,start_time,end_time,prov_id,city_id,area_id,idt_id,cnt,net_type&fq=net_type:1 AND (idt_id:12011 OR idt_id:5004 OR idt_id:6051 OR idt_id:6056 OR idt_id:8002) AND time_type:1 AND start_time:[1373598465 TO 1374055254] AND -start_time:1374055254&sort=log_id asc,start_time asc,idt_id desc&start=0&rows=30

或

http://192.x.x.x:port/xxx/core1/select?q=*:*&fl=log_id,start_time,end_time,prov_id,city_id,area_id,idt_id,cnt,net_type&fq=net_type:1&fq=idt_id:12011 OR idt_id:5004 OR idt_id:6051 OR idt_id:6056 OR idt_id:8002&fq =time_type:1&fq=start_time:[1373598465 TO 1374055254]&fq =-start_time:1374055254&sort=log_id asc,start_time asc,idt_id desc&start=0&rows=30

5、多个字段分组统计(只支持count函数)

SQL查询语句：

SELECT city_id, area_id, COUNT(cnt) AS count_cnt

FROM v_i_event

WHERE prov_id = 1 AND net_type = 1

GROUP BY city_id, area_id;

Solr查询URL：

http://192.x.x.x:port/xxx/core1/select?q=*:*&facet=true&facet.pivot=city_id,area_id&fq=prov_id:1 AND net_type:1&rows=0&indent=true

6、多个字段分组统计(支持count、sum、max、min等函数)

一次对多个字段进行独立分组统计，Solr可以很好的支持。这相当于执行两个带有GROUP BY子句的SQL，这两个GROUP BY分别只对一个字段进行汇总统计。

SQL查询语句：

SELECT city_id, area_id, COUNT(cnt) AS count_cnt

FROM v_i_event

WHERE prov_id = 1 AND net_type = 1

GROUP BY city_id;

SELECT city_id, area_id, COUNT(cnt) AS count_cnt

FROM v_i_event

WHERE prov_id = 1 AND net_type = 1

GROUP BY area_id;

Solr查询URL：

http://192.x.x.x:port/xxx/core1/select?q=*:*&stats=true&stats.field=cnt&f.cnt.stats.facet=city_id&&f.cnt.stats.facet=area_id&fq=prov_id:1 AND net_type:1&rows=0&indent=true

7、多个字段联合分组统计(支持count、sum、max、min等函数)

SQL查询语句：

SELECT city_id, area_id, SUM(cnt) AS sum_cnt, AVG(cnt) AS avg_cnt, MAX(cnt) AS max_cnt, MIN(cnt) AS min_cnt, COUNT(cnt) AS count_cnt

FROM v_i_event

WHERE prov_id = 1 AND net_type = 1

GROUP BY city_id, area_id;