字符串處理

數(shù)據(jù)準備：

create table n10(
  pos int4
);
insert into n10 (pos) values (1);
insert into n10 (pos) values (2);
-- ... 1 到 10
insert into n10 (pos) values (10);
--
create table table3(
  name text,
  address text
);
insert into table3 (line) values ('aladdin', 'china');

• 串聯(lián)字符串

-- Postgres / Oracle
select name || ' live in ' || address as msg from table3; -- aladdin live in china
-- MySQL / Postgres
select concat(name, ' live in ', address) as msg from table3; -- aladdin live in china
select concat_ws('_', name, address) from table8; -- aladdin_china

• 查找匹配項

-- PostgreSQL / MySQL
select * from emp where emp_sal in (13000, 30000); -- 使用 in
-- 使用 like
select * from emp where emp_name like 'ala%'; -- 匹配單個字符使用 '_'

• 遍歷字符串

-- Postgres
select
       substring(t3.name, iter.pos, 1) as c
from
     table3 t3, n10 iter
where
      iter.pos <= length(t3.name);
-- a
-- l
-- a
-- d
-- d
-- i
-- n

• 統(tǒng)計字符出現(xiàn)的次數(shù)

-- Postgres
select
       (length(name) - length(replace(name, 'a', ''))) as num
       -- 統(tǒng)計字符 a 出現(xiàn)的次數(shù)
from table3; -- 2

• 刪除不想要的字符

-- Postgres
select
       replace(translate(line, 'AEIOUaeiou', 'aaaaaaaaaa'), 'a', '')
       -- 將元音字符刪除
from table3;

• 分離字符串和數(shù)字

-- 準備工作
create table table1(
  line text
);
insert into table1 (line) values ('Jim1006');
-- Postgres
select
       replace(translate(line, '0123456789', '0000000000'), '0', '') as ename,
       cast(replace(translate(lower(line), 'asdfghjklzxcvbnmqwertyuiop', repeat('z', 26)), 'z', '') as integer) as year
from table1 t1;

• 按字符串中的數(shù)字排序

-- Postgres
select line
from table1
order by cast(replace(translate(lower(line), 'asdfghjklzxcvbnmqwertyuiop', repeat('z', 26)), 'z', '') as integer);

• 解析IP地址

create table table2(
  ip text
);
insert into table2 (ip) values ('144.132.11.3');
-- MySQL
select
       substring_index(substring_index(ip, '.', 1), '.', -1) as a,
       substring_index(substring_index(ip, '.', 2), '.', -1) as b,
       substring_index(substring_index(ip, '.', 3), '.', -1) as c,
       substring_index(substring_index(ip, '.', 4), '.', -1) as d
from table2;
-- PostgreSQL
select
       split_part(ip, '.', 1) as a,
       split_part(ip, '.', 2) as b,
       split_part(ip, '.', 3) as c,
       split_part(ip, '.', 4) as d
from table2;

數(shù)值處理

數(shù)據(jù)準備：

create table table3(
  id int4,
  score double precision
);
insert into table3 (id, score) values (1001, 97);
insert into table3 (id, score) values (1001, 95);
insert into table3 (id, score) values (1001, 98);
insert into table3 (id, score) values (1002, 93);
insert into table3 (id, score) values (1002, 99);
insert into table3 (id, score) values (1003, 94);
insert into table3 (id, score) values (1003, 99);

• 計算均值

select avg(score) from table3;
-- 分組求均值
select id, round(avg(score)::numeric, 2) from table3 group by id;
select id, round(avg(coalesce(t3.score, 0))::numeric, 2) from table3 t3 group by id;

• 去掉極值求均值

select
       avg(score)
from
     table3
where
      score not in (
                    (select max(score) from table3),
                    (select min(score) from table3)
                   );

• 最大值與最小值

select min(score) as min, max(score) as max from table3;
-- 分組求最大值與最小值
select id, min(score) as min, max(score) as max from table3 group by id;
select id, min(coalesce(score, 0)) as min, max(coalesce(score, 0)) as max from table3 group by id;

• 求和

select sum(score) as sum from table3;
-- 分組求和
select id, sum(score) as sum from table3 group by id;
select id, sum(coalesce(score, 0)) from table3 group by id;

• 計算行數(shù)

-- count(*) 統(tǒng)計行數(shù)忿等，無論非 null 否
select count(*) as count from table3;
-- 計算某列值個數(shù)
select count(score) as count -- 如果 score 為 null 則不計數(shù)
from table3;
-- 分組求行數(shù)
select id, count(*) as count from table3 group by id;

• 求眾數(shù)

select score
from
     (
       select
              score, (dense_rank() over (order by e.count desc)) as rnk
       from
            (
              select score, count(score) as count
              from table3
              group by score
            ) as e
     ) as t
where t.rnk = 1;

• 計算百分比

-- 計算 id 為 1001 學生分數(shù)占全部分數(shù)的百分比
select (sum(case when id = 1001 then score end) / sum(score)) * 100 as pct from table3;

日期處理

• 提取年月日

-- Postgres
select extract(year from now()) as year;
-- mon: 月
-- day: 日
-- week: 一年中第幾周
-- doy: 一年中第幾天

對 NULL 值處理

• 查找 NULL 值

-- PostgreSQL / MySQL
select * from emp where emp_name is null;

• 將 NULL 值轉換為實際值

-- PostgreSQL / MySQL
select coalesce(emp_name, 'Secret Superstar') as name from emp;

• 比較中有 NULL 值

-- 數(shù)據(jù)準備
create table table2(
  name text,
  sal double precision
);
create table table3(
  name text,
  sal double precision
);
insert into table2 (name, sal) values ('aladdin', 12000), ('bilib', null);
insert into table3 (name, sal) values ('chrome', 3000);
-- 使用 coalesce 函數(shù)進行 null 值的轉換
select * from table2 t2
where coalesce(t2.sal, 0) > (select t3.sal from table3 t3 where t3.name = 'chrome');

CASE...WHEN

• PostgerSQL / MySQL /Oracle

select
     emp_name,
     emp_sal,
     case
       when emp_sal < 10000.0 then 'low'
       when emp_sal >= 10000.0 and emp_sal < 20000.0 then 'mid'
       when emp_sal >= 20000.0 and emp_sal < 50000.0 then 'high'
       else 'very high'
     end as "status"
from emp;

返回指定行數(shù)

• PostgreSQL / MySQL

-- 返回指定行數(shù)菌瘫，PostgreSQL 和 MySQL 一樣
select * from emp order by emp_sal limit 3;
-- 隨機返回 N 行，Postgre 實現(xiàn)
select * from emp order by random() limit 3;
-- 隨機返回 N 行，MySQL 實現(xiàn)
select * from emp order by rand() limit 3;

• Oracle

select * from emp where rownum <= 3;

排序

• 基本排序

-- PostgreSQL / MySQL
-- 單字段
select * from emp order by emp_sal desc; -- 將序忿危，默認升序 / asc
-- 多字段
select * from emp order by emp_sal desc, emp_name;
-- 子字段
select * from emp order by substr(emp_name, length(emp_name) - 2);

• 對字段中的數(shù)字 / 字母排序

-- 數(shù)據(jù)處理
create table public.table2(
msg text
);
insert into public.table2 values ('aladdin 800'), ('bilib 300'), ('chrom 600');

-- 下面連個方法對 MySQL 不適用
-- 對字段中的數(shù)字排序
select
     *
from
   public.table2
order by
       replace(msg, replace(translate(msg, '0123456789', '#########'), '#', ''), '');
-- 對字段中的字母進行排序
select
     *
from public.table2
order by
       replace(translate(msg, '0123456789', '#########'), '#', '');

• 對 NULL 進行更高級的排序

-- 數(shù)據(jù)準備
create table table4(
name text,
sal double precision
);
insert into
public.table4(name, sal)
values 
       ('alad', 0),
       ('bilib', 500),
       ('chrome', 1000),
       ('dell', null),
       ('echo', null);


-- 最基本的排序
select * from public.table4 order by sal desc; -- 這樣 null 值就會到最上面
-- 讓 null 值下來
with temp as(
select name,
       sal,
       case
         when sal is null
           then 0 else 1
         end as is_null
from public.table4
)
select name, sal from temp order by is_null desc, sal desc; -- 其它解決方案類似

• 根據(jù)條件排序

select * from table4
order by
       case 
         when name = 'alad'
           then sal
         else name
       end desc;

集合操作

• 合并
  UNION 會去重復項，UNION ALL 不會，如果不是特殊要求，一般都使用 UNION ALL若厚，UNION 相當于在 UNION ALL 的基礎上做一次 distinct 操作:

select name, sal from table5
union all
select name, sal from public.table6;

• 交集與差集

-- 數(shù)據(jù)準備
create table public.table1(
name text,
hobby text
);
insert into
public.table1 (name, hobby)
values
       ('aladdin', 'Ping pong'),
       ('aladdin', 'Computer games'),
       ('bilil', 'Basketball'),
       ('bilib', 'Computer games');

-- Postgres
-- intersect 求交集
select
     hobby
from
   table1
where
    name = 'aladdin'
intersect
select
     hobby
from
   table1
where name = 'bilib'; -- Computer games

-- 使用基礎語句實現(xiàn)
select
     t1.hobby
from table1 t1
inner join table1 t2 
  on
    t1.name = 'aladdin' and
    t2.name = 'bilib' and
    t1.hobby = t2.hobby;

-- (A - B) 求差集，Oracle 使用 minus 關鍵字實現(xiàn)
select
     hobby
from
   table1
where
    name = 'aladdin'
except
select
     hobby
from table1
where
    name = 'bilib'; -- Ping pong

-- 使用子查詢實現(xiàn)
select
     t1.hobby
from
   table1 t1
where
    t1.name = 'aladdin' and t1.hobby not in (select t2.hobby from table1 t2 where t2.name = 'bilib');