duckdb学习笔记

insert

insert or ignore into tbl  -- ignore 
insert or replace into tbl  -- replace 
insert into tbl by name (select *)  -- by name
insert into tbl select 1 i,2 j returning *,i*j as ij  -- returning 

pivot

-- pivot
pivot duckdb_types() on type_category using first(type_size) group by logical_type;
-- unpivot
with q1 as (from values('a','b',1,2,3) t) unpivot q1 on col2,col3,col4 into name "name" value n;
with q1 as (from values('a','b',1,2,3) t) unpivot q1 on columns(* exclude(col0,col1)) into name "name" value n;
with q1 as (from values('a',1,2,3,4) t) unpivot q1 on (col1,col2) as v1,(col3,col4) as v2 into name nm value n1,n2;

select

select * exclude(i) from (select 1,2,3) t(i,j,ij) ;
select columns(* exclude(i)) from (select 1 i,2 j);
select * replace(i*2 as i) from (select 1 i,2 j);
select columns('^i') from (select 1 i1,2 i2,3 j);

join

-- lateral join
select * from values('a1',1,2,'b1') 
	t(loc1,std,sta,loc2),
	lateral(select loc1,stunion all select loc2,sta) 
	tb(loc,slot);
        
--positional join
select * from (from values('a','b'),('c','d') t1) 
positional join (from values(1,2),(3,4) t2);

-- using
select * from (from values(1,'a'),(2,'b') t1(i,j)) 
join (from values(2,'c') t2(i,j)) 
using (i);

sample

select * from tb tablesample 10%;
select * from tb tablesample 10 rows;
select * from tb using sample 5 rows;

group

-- grouping sets
select i,j,sum(n) n from values('x','x1',1),('x','x2',2),('y','y1',100) t(i,j,n) 
group by grouping sets((i,j),(i),(j),());
-- cube
select i,j,sum(n) n from values('x','x1',1),('x','x2',2),('y','y1',100) t(i,j,n) 
group by cube(i,j);
-- rollup
select i,j,sum(n) n from values('x','x1',1),('x','x2',2),('y','y1',100) t(i,j,n) group by rollup(i,j);

window

select *,row_number() over(partition by i order by n desc) 
from values('x','x1',1),('x','x2',2),('y','y1',100) t(i,j,n);
-- qualify
select *,row_number() over(partition by i order by n desc) as rk 
from values('x','x1',1),('x','x2',2),('y','y1',100) t(i,j,n) qualify rk<2;
-- window
select *,row_number() over my_window as rk 
from values('x','x1',1),('x','x2',2),('y','y1',100) t(i,j,n) 
window my_window as (partition by i order by n desc) qualify rk<2;

filter

select count(1) filter(where n<=2) n from values('x','x1',1),('x','x2',2),('y','y1',100) t(i,j,n) ;

intersect

select unnest(range(17)) i intersect -- except/union/union all 
select type_size from df;

data_types

enum

create type size as enum('small','mid','big');
create type size1 as enum(select unnest([1,2,2,4]));
drop type size1;
enum_code
enum_first
enum_last
enum_range
    select enum_range(null::size);
enum_range_boundary

list

select list_value(1,2,3);
create table list_table(int_list int[]);
select (['a','b','c'])[1];
select (['a','b','c'])[-1];
select (['a','b','c'])[2+1];
select (['a','b','c'])[1:2];
select (['a','b','c'])[:2];
select (['a','b','c'])[-2:];
select list_slice(['a','b','c'],2,3);

map

select map {'a':1,'b':2};
select map_from_entries([('a',1),('b',2)]);
select map {[1,2]:'a'};
select map {3:['c','d']};
select map {[1,2]:['a'],[3]:['c','d']};

create table map_table(map_col MAP(int,double));

select map {'a':1,'b':2}['b'];
select map {'a':1,'b':2}['b'][1];
select map {'a':1,'b':2}['c'];
select element_at(map {'a':1,'b':2},'a');

null

select null = null ; -- null;
select null is null;
select null is not distinct from null;
select COALESCE(null,1,null,2);
select ifnull(null,1);

struct

select {'a':1,'b':2};
select struct_pack(a:=1,b:=2);
select row(x,x+1,y) from (from values(1,'a'),(2,'b') t(x,y));
select unnest(row(x,x+1,y)) from (from values(1,'a'),(2,'b') t(x,y));
select struct_insert({'a':1,'b':2,'c':3},d:=4);
select x.a from (select {'a':1,'b':2} x);
select x."a" from (select {'a':1,'b':2} x);
select x['a'] from (select {'a':1,'b':2} x);
select struct_extract(x,'a') from (select {'a':1,'b':2} x);
select x.* from (select {'a':1,'b':2} x);
select unnest(x)['a'] from (select [{'a':1,'b':2},{'a':3,'b':4}] x);
select (logical_type,type_size) from df;
select row(logical_type,type_size) from df;

text

select 'This''s Tom';
select 'a' || 'b';
select 'a' || chr(10) || 'b';

time

SELECT TIME  '1992-09-20 11:30:00.123456';    -- 11:30:00.123456
SELECT TIMETZ '1992-09-20 11:30:00.123456';    -- 11:30:00.123456+00
SELECT TIMETZ '1992-09-20 11:30:00.123456-02:00'; -- 13:30:00.123456+00
SELECT TIMETZ '1992-09-20 11:30:00.123456+05:30'; -- 06:00:00.123456+00

date

select date '2023-01-15';
select 'epoch'::date; -- 1970-01-01

interval

select interval 1 year;
select date '2000-1-1'+ interval 1 year;
select interval(i) year from range(1,5) t(i);
select interval '1 month 1 day';
select today() + interval (i) day from range(10) t(i);
SELECT TIMESTAMP '2000-02-01 12:00:00' - TIMESTAMP '2000-01-01 11:00:00' AS diff;
SELECT datediff('month', TIMESTAMP '2000-01-01 11:00:00', TIMESTAMP '2000-02-01 12:00:00') AS diff;

case/if

select case when 1>0 then 1 else 0 en;
select if(1>0,1,0);

cast/try_cast

select cast('1' as int);
select try_cast('a' as int);

star

select columns(c-> c like 'a_%') from (select unnest({'a_1':1,'a_2':2,'b':3}) ) t;
select columns('^a_') from (select unnest({'a_1':1,'a_2':2,'ba':3}) ) t;
select * exclude('b') from (select unnest({'a_1':1,'a_2':2,'b':3}) ) t;
select * replace(b*b as b) from (select unnest({'a_1':1,'a_2':2,'b':3}) ) t;