Solr查询与sql对比转化

将常用sql查询转化为Solr查询，网上已有做出对比的，这里引用一下，去掉部分图片，快速对比，

原文出处：http://shiyanjun.cn/archives/78.html

查询对比：
-------------------------------------------------------------------------------------------------------------------------------------------
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;


Solr查询URL：
http://localhost:8983/solr/solr_select/select?q=*:*&stats=true&stats.field=id&rows=0&indent=true