insertorignoreinto tbl -- ignore insertorreplaceinto tbl -- replace insertinto tbl by name (select*)-- by nameinsertinto tbl select1 i,2 j returning*,i*j as ij -- returning
pivot
-- pivotpivot duckdb_types()on type_category usingfirst(type_size)groupby logical_type;-- unpivotwith q1 as(fromvalues('a','b',1,2,3) t)unpivot q1 on col2,col3,col4 into name "name"value n;with q1 as(fromvalues('a','b',1,2,3) t)unpivot q1 oncolumns(* exclude(col0,col1))into name "name"value n;with q1 as(fromvalues('a',1,2,3,4) t)unpivot q1 on(col1,col2)as v1,(col3,col4)as v2 into name nm value n1,n2;
-- grouping setsselect i,j,sum(n) n fromvalues('x','x1',1),('x','x2',2),('y','y1',100) t(i,j,n)groupby grouping sets((i,j),(i),(j),());-- cubeselect i,j,sum(n) n fromvalues('x','x1',1),('x','x2',2),('y','y1',100) t(i,j,n)groupby cube(i,j);-- rollupselect i,j,sum(n) n fromvalues('x','x1',1),('x','x2',2),('y','y1',100) t(i,j,n)groupby rollup(i,j);
window
select*,row_number()over(partitionby i orderby n desc)fromvalues('x','x1',1),('x','x2',2),('y','y1',100) t(i,j,n);-- qualifyselect*,row_number()over(partitionby i orderby n desc)as rk
fromvalues('x','x1',1),('x','x2',2),('y','y1',100) t(i,j,n) qualify rk<2;-- windowselect*,row_number()over my_window as rk
fromvalues('x','x1',1),('x','x2',2),('y','y1',100) t(i,j,n)
window my_window as(partitionby i orderby n desc) qualify rk<2;
filter
selectcount(1) filter(where n<=2) n fromvalues('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;