当前位置: 首页 > 工具软件 > ydb > 使用案例 >

YDB与Hive SQL在写法上的对比

顾磊
2023-12-01

普通查询SQL写法对比

对比点

HIVE

YDB

统计计数

select count(*) from ydb_example_shu where ydbpartion= '2015'

/*ydb.pushdown('->')*/

select count(*) from ydb_example_shu where ydbpartion = '2015'

/*('<-')pushdown.ydb*/ ;

数据预览

select * from ydb_example_shu where ydbpartion = '3000w' limit 10

/*ydb.pushdown('->')*/

select * from ydb_example_shu where ydbpartion = '3000w' limit 10

 /*('<-')pushdown.ydb*/;

检索过滤筛选

select content,usernick from ydb_example_shu where ydbpartion = '3000w' and content='王老吉' limit 10

/*ydb.pushdown('->')*/

select content,usernick from ydb_example_shu where ydbpartion = '3000w' and content='王老吉' limit 10

/*('<-')pushdown.ydb*/;

多条件组合

select ydb_sex,ydb_grade,ydb_age,ydb_blood,amtlong from ydb_example_shu where ydbpartion = '3000w' and ydb_sex='女' and ydb_grade='本科' and (ydb_age='20到30岁' or ydb_blood='O') and amtlong>=3000 and amtlong<=4000  limit 10

/*ydb.pushdown('->')*/

select ydb_sex,ydb_grade,ydb_age,ydb_blood,amtlong from ydb_example_shu where ydbpartion = '3000w' and ydb_sex='女' and ydb_grade='本科' and (ydb_age='20到30岁' or ydb_blood='O') and  (amtlong like '([3000 TO 4000] )') limit 10

/*('<-')pushdown.ydb*/;

sum求和

select sum(amtdouble) from ydb_example_shu where ydbpartion = '3000w'

/*ydb.pushdown('->')*/

select sum(amtdouble) from ydb_example_shu where ydbpartion = '3000w'

/*('<-')pushdown.ydb*/;

求平均数

select avg(amtdouble) as avgamt from ydb_example_shu where ydbpartion = '3000w'

/*ydb.pushdown('->')*/

select avg(amtdouble) as avgamt from ydb_example_shu where ydbpartion = '3000w'

/*('<-')pushdown.ydb*/;

多条件统计

select count(*),count(amtdouble),avg(amtdouble),sum(amtdouble),min(amtdouble),max(amtdouble),min(ydb_province),max(ydb_province) from ydb_example_shu where ydbpartion = '3000w'

/*ydb.pushdown('->')*/

select count(*),count(amtdouble),avg(amtdouble),sum(amtdouble),min(amtdouble),max(amtdouble),min(ydb_province),max(ydb_province) from ydb_example_shu where ydbpartion = '3000w'

/*('<-')pushdown.ydb*/;

单列group by

select ydb_sex,count(*),count(amtdouble),sum(amtdouble) from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex limit 10

/*ydb.pushdown('->')*/

select ydb_sex,count(*),count(amtdouble),sum(amtdouble) from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex limit 10

/*('<-')pushdown.ydb*/;

多列group by

select ydb_sex,ydb_province,count(*) as cnt,count(amtdouble),sum(amtdouble) from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex,ydb_province order by cnt desc limit 10

/*ydb.pushdown('->')*/

select ydb_sex,ydb_province,count(*) as cnt,count(amtdouble),sum(amtdouble) from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex,ydb_province order by cnt desc limit 10

/*('<-')pushdown.ydb*/;

order by 列

select ydb_sex, phonenum,amtlong,amtdouble from ydb_example_shu where ydbpartion='3000w'  order by amtdouble desc ,amtlong limit 10

/*ydb.pushdown('->')*/

select ydb_sex, phonenum,amtlong,amtdouble from ydb_example_shu where ydbpartion='3000w'  order by amtdouble desc ,amtlong limit 10

/*('<-')pushdown.ydb*/;

模糊检索

select content,usernick,ydb_province from ydb_example_shu where ydbpartion = '3000w' and content like '%辽宁%' limit 10

YDB也支持这样写,但是有本身更高效的写法。

/*ydb.pushdown('->')*/

select content,usernick,ydb_province from ydb_example_shu where ydbpartion = '3000w' and ydb_province like '%辽宁%' limit 100

/*('<-')pushdown.ydb*/;

 


 

 

复杂查询SQL写法对比

对比点

HIVE

YDB

导出

insert overwrite table ydb_import_importtest

select    phonenum,usernick,ydb_sex,ydb_province,ydb_grade,ydb_age,ydb_blood,ydb_zhiye,ydb_earn,ydb_prefer,ydb_consume,ydb_day,amtdouble,amtlong,content

from ydb_example_shu where ydbpartion = '3000w'

 

insert overwrite table ydb_import_importtest

/*ydb.pushdown('->')*/

select

     phonenum,usernick,ydb_sex,ydb_province,ydb_grade,ydb_age,ydb_blood,ydb_zhiye,ydb_earn,ydb_prefer,ydb_consume,ydb_day,amtdouble,amtlong,content 

from ydb_example_shu where ydbpartion = '3000w'

/*('<-')pushdown.ydb*/;

多表关联

select k21.vehiclePlate from(

    select  k1.vehiclePlate as vehiclePlate from (

           select vehiclePlate,tollCode from vehiclepass where ydbpartion = '3000w' and tollCode='1'

    ) k1

    LEFT SEMI JOIN

   (

       select vehiclePlate,tollCode from vehiclepass where ydbpartion = '3000w' and tollCode='2' 

    ) k2

    on (k1.vehiclePlate=k2.vehiclePlate)

 ) k21

LEFT SEMI JOIN

(

   select vehiclePlate,tollCode from vehiclepass  where ydbpartion = '3000w' and tollCode='3'

 ) k22 on k21.vehiclePlate=k22.vehiclePlate order by k21.vehiclePlate;

select k21.vehiclePlate from(

    select  k1.vehiclePlate as vehiclePlate from (

            /*ydb.pushdown('->')*/ select vehiclePlate,tollCode from vehiclepass where ydbpartion = '3000w' and tollCode='1' /*('<-')pushdown.ydb*/

    ) k1

    LEFT SEMI JOIN

   (

       /*ydb.pushdown('->')*/ select vehiclePlate,tollCode from vehiclepass where ydbpartion = '3000w' and tollCode='2' /*('<-')pushdown.ydb*/ 

    ) k2

    on (k1.vehiclePlate=k2.vehiclePlate)

 ) k21

LEFT SEMI JOIN

(

   /*ydb.pushdown('->')*/ select vehiclePlate,tollCode from vehiclepass  where ydbpartion = '3000w' and tollCode='3' /*('<-')pushdown.ydb*/

 ) k22 on k21.vehiclePlate=k22.vehiclePlate order by k21.vehiclePlate;

union示例

select sum(cnt) as cnt from

(

 

select count(*) as cnt from ydb_example_shu where ydbpartion = '3000w'

 union all

select count(*) as cnt from ydb_example_shu where ydbpartion = '300winsert'

union all

select count(*) as cnt from ydb_example_shu where ydbpartion = '300winsert2'

union all

select count(*) as cnt from ydb_example_shu where ydbpartion = '3000w' and content='王老吉' 

union all

select count(*) as cnt from ydb_example_shu where ydbpartion = '20151011' and content='工商银行'

union all

select count(*) as cnt from ydb_example_shu where ydbpartion = '20151011' 

) tmp limit 10;

select sum(cnt) as cnt from

(

/*ydb.pushdown('->')*/ select count(*) as cnt from ydb_example_shu where ydbpartion = '3000w' /*('<-')pushdown.ydb*/

union all

/*ydb.pushdown('->')*/ select count(*) as cnt from ydb_example_shu where ydbpartion = '300winsert' /*('<-')pushdown.ydb*/

union all

/*ydb.pushdown('->')*/ select count(*) as cnt from ydb_example_shu where ydbpartion = '300winsert2' /*('<-')pushdown.ydb*/

union all

/*ydb.pushdown('->')*/ select count(*) as cnt from ydb_example_shu where ydbpartion = '3000w' and content='王老吉' /*('<-')pushdown.ydb*/

union all

/*ydb.pushdown('->')*/ select count(*) as cnt from ydb_example_shu where ydbpartion = '20151011' and content='工商银行' /*('<-')pushdown.ydb*/

union all

/*ydb.pushdown('->')*/ select count(*) as cnt from ydb_example_shu where ydbpartion = '20151011'  /*('<-')pushdown.ydb*/

) tmp limit 10;

union示例2

select * from

(

select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='旺旺' order by amtlong desc limit 1

union all

select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='王老吉' order by amtlong desc limit 1

union all

select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='汇源' order by amtlong desc limit 1 

union all

select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='哇哈哈' order by amtlong desc limit 1

 

) tmp  limit 1000;

 

select * from

(

/*ydb.pushdown('->')*/ select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='旺旺' order by amtlong desc limit 1   /*('<-')pushdown.ydb*/

union all

/*ydb.pushdown('->')*/ select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='王老吉' order by amtlong desc limit 1  /*('<-')pushdown.ydb*/

union all

/*ydb.pushdown('->')*/ select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='汇源' order by amtlong desc limit 1   /*('<-')pushdown.ydb*/

union all

/*ydb.pushdown('->')*/ select amtlong,content from ydb_example_shu where ydbpartion = '3000w' and content='哇哈哈' order by amtlong desc limit 1  /*('<-')pushdown.ydb*/ 

) tmp  limit 1000;

 

distinct示例

        select size(collect_set(ydb_sex)) as dist_sex,

    size(collect_set(ydb_province)) as dist_province,

    count(*) as cnt,

    count(amtlong) as cnt_long,

    count(distinct amtlong) as dist_long

 from ydb_example_shu where ydbpartion = '3000w' and content='王老吉'

 

select

    size(collect_set(tmp.ydb_sex)) as dist_sex,

    size(collect_set(tmp.ydb_province)) as dist_province,

    count(*) as cnt,

    count(tmp.amtlong) as cnt_long,

    count(distinct tmp.amtlong) as dist_long

from (

    /*ydb.pushdown('->')*/

        select ydb_sex,ydb_province,amtlong from ydb_example_shu where ydbpartion = '3000w' and content='王老吉'

    /*('<-')pushdown.ydb*/

) tmp limit 10;

group by +distinct

select

    ydb_sex as ydb_sex,

    size(collect_set(ydb_province)) as dist_province,

    count(*) as cnt,

    count(amtlong) as cnt_long,

    count(distinct amtlong) as dist_long

from ydb_example_shu where ydbpartion = '3000w' and content='王老吉'

group by ydb_sex limit 10;

select

    tmp.ydb_sex as ydb_sex,

    size(collect_set(tmp.ydb_province)) as dist_province,

    count(*) as cnt,

    count(tmp.amtlong) as cnt_long,

    count(distinct tmp.amtlong) as dist_long

from

(

    /*ydb.pushdown('->')*/

        select ydb_sex,ydb_province,amtlong from ydb_example_shu where ydbpartion = '3000w' and content='王老吉'

    /*('<-')pushdown.ydb*/

) tmp

group by tmp.ydb_sex limit 10;

行转列示例1

select ydb_sex,concat_ws('#', sort_array(collect_set(concat_ws(',',ydb_province,cnt,cntamt,sumamt)))) from (

        select ydb_sex,ydb_province,count(*) as cnt,count(amtdouble) as cntamt,sum(amtdouble) as sumamt from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex,ydb_province 

)tmp  group by ydb_sex limit 10;

select ydb_sex,concat_ws('#', sort_array(collect_set(concat_ws(',',ydb_province,cnt,cntamt,sumamt)))) from (

    /*ydb.pushdown('->')*/

        select ydb_sex,ydb_province,count(*) as cnt,count(amtdouble) as cntamt,sum(amtdouble) as sumamt from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex,ydb_province 

    /*('<-')pushdown.ydb*/

)tmp  group by ydb_sex limit 10;

行转列示例2

select ydb_province,sum(cnt) as scnt,concat_ws('#', sort_array(collect_set(concat_ws(',',ydb_sex,cnt,cntamt,sumamt)))) from (

        select ydb_sex,ydb_province,count(*) as cnt,count(amtdouble) as cntamt,sum(amtdouble) as sumamt from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex,ydb_province 

)tmp  group by ydb_province order by scnt desc limit 10;

select ydb_province,sum(cnt) as scnt,concat_ws('#', sort_array(collect_set(concat_ws(',',ydb_sex,cnt,cntamt,sumamt)))) from (

    /*ydb.pushdown('->')*/

        select ydb_sex,ydb_province,count(*) as cnt,count(amtdouble) as cntamt,sum(amtdouble) as sumamt from ydb_example_shu where ydbpartion = '3000w' group by ydb_sex,ydb_province 

    /*('<-')pushdown.ydb*/

)tmp  group by ydb_province order by scnt desc limit 10;

 

 

 

 

 

 

 

 

 

 


 类似资料: