SQL函数

select current_user();

 current_user  
+--------------+
   hubble

select current_database();

  current_database
--------------------
  test

select extract(epoch from cast('2022-11-13 11:22:10'  as timestamp))::int;

   extract  
+--------------+
1668338530

select 1668338530::timestamp;

       timestamp
-----------------------
  2022-11-13 11:22:10

select  substring('hubble', 1, 4);

   substring  
+--------------+
   hubb

select substring('information', 8);

   substring  
+--------------+
   tion

select substring('PostgreSQL' from 'gre' );

   SUBSTRING  
+--------------+
   gre

select reverse(substring(reverse('PostgreSQL'),1,2));

   reverse  
+--------------+
   QL

select position('om' in 'Thomas');

  position  
+--------------+
   3

select pg_client_encoding();

pg_client_encoding  
+--------------+
   UTF8

select regexp_replace('Thomas', '.[mN]a.', 'M'); 

regexp_replace 
+--------------+
   ThM

select repeat('hb', 3); 

    repeat 
+--------------+
   hbhbhb

select trim(both 'x' from 'xTomxx');

  btrim
+--------------+
  Tom

select trim('   xTomxxn    '); 

  btrim
+--------------+
  xTomxxn

<expr>:::<type>
ANNOTATE_TYPE(<expr>, <type>)

select (1 / 0):::FLOAT;

pq: division by zero

ARRAY( ... subquery ... )

ARRAY[ <expr>, <expr>, ... ]

select ARRAY[1,2,3] AS a;

+---------+
|    a    |
+---------+
| {1,2,3} |
+---------+

<expr> :: <type>
CAST (<expr> AS <type>)

CAST(now() AS DATE)

<expr> COLLATE <collation>

'a' COLLATE de

EXISTS ( ... subquery ... )
NOT EXISTS ( ... subquery ... )

NULLIF ( <expr1>, <expr2> )

IFNULL ( <expr1>, <expr2> )
COALESCE ( <expr1> [, <expr2> [, <expr3> ] ...] )

(<expr>, <expr>, ...)
ROW (<expr>, <expr>, ...)

select ('x', 123, 12.3) AS a;

+----------------+
|       a        |
+----------------+
| ('x',123,12.3) |
+----------------+

select regexp_split_to_array('hello world', E'\\s+'); 

  regexp_split_to_array              
+----------------------------+
      {hello,world} 

create table test_array (a STRING[]);
insert into  test_array values (ARRAY['Monday', 'Tuesday', 'Wednesday']);
select * from test_array;

              a               
+----------------------------+
  {Monday,Tuesday,Wednesday}  

select  regexp_split_to_table('11,22,33',',');

  regexp_split_to_table
-------------------------
  11
  22
  33

select array_append(a,'Thursday') from test_array;

             array_append              
+-------------------------------------+

  {Monday,Tuesday,Wednesday,Thursday}

select array_cat(a,ARRAY['Thursday', 'Friday', 'Saturday']) from test_array;

                       array_cat                       
+-----------------------------------------------------+
  {Monday,Tuesday,Wednesday,Thursday,Friday,Saturday}

select array_length(a,1) from test_array;

  array_length  
+--------------+
          3

select array_lower(a,1) from test_array;

  array_lower  
+-------------+
          1

select array_upper(a,1) from test_array;

  array_upper  
+-------------+
          3

select array_position(a,'Tuesday') from test_array;

  array_position  
+----------------+
          2 

select array_prepend('Thursday',a) from test_array;

             array_prepend             
+-------------------------------------+
  {Thursday,Monday,Tuesday,Wednesday}

select array_remove(a,'Tuesday') from test_array;

     array_remove     
+--------------------+
  {Monday,Wednesday}

select array_replace(a,'Tuesday','Thursday') from test_array;

         array_replace         
+-----------------------------+
  {Monday,Thursday,Wednesday}

select array_to_string(a,',') from test_array;

      array_to_string       
+--------------------------+
  Monday,Tuesday,Wednesday

select array_to_string(ARRAY['Thursday', 'Friday', 'Saturday',null,'Friday'],',');

          array_to_string          
+---------------------------------+
  Thursday,Friday,Saturday,Friday

select array_to_string(ARRAY['Thursday', 'Friday', 'Saturday',null,'Friday'],',','y');

          array_to_string           
+----------------------------------+
  Thursday,Friday,Saturday,y,Friday

select string_to_array('Thursday,Friday,Saturday,Friday',',');

           string_to_array           
+-----------------------------------+
  {Thursday,Friday,Saturday,Friday}

select string_to_array('Thursday,Friday,Saturday,y,Friday',',','y');

             string_to_array              
+----------------------------------------+
  {Thursday,Friday,Saturday,NULL,Friday}

select greatest(1,10,100,15);

  greatest  
+----------+
     100 

select least(1,2,10,100,122);

  least  
+-------+
      1 

select age(timestamptz '2022-03-26 14:22:10+08:00',timestamptz '2020-02-26 10:10:10-05:00');

            age     
+--------------------------+
  2 years 28 days 15:12:00 

select age(timestamptz '2021-11-26 14:22:10+08:00');

               age         
+--------------------------------+
  10 mons 14 days 23:57:43.199154

select current_date();

         current_date         
+-----------------------------+
          2022-10-11

select date_trunc('second',localtime());

    localtime        
+---------------+
    17:26:42

select date_trunc('second',localtimestamp());

    localtimestamp
--------------------------
  2023-04-19 15:33:33

select current_timestamp();

       current_timestamp()         
+-----------------------------+
 2022-10-11 14:28:42.693 +0800

select now();

            now()         
+-----------------------------+
 2022-10-11 14:32:38.070 +0800

select date_trunc('day',timestamptz '2022-11-12 14:22:10+08:00')  as  date_day;

         date_day          
+---------------------------+
 2022-11-12 00:00:00+08  
(1 row)

select date_trunc('month',timestamptz '2022-11-12 14:22:10+08:00') as date_month;

         date_month          
+---------------------------+
 2022-11-01 00:00:00+08  
(1 row)

select extract('month',timestamptz '2022-10-11 16:22:10+08:00');

  extract  
+---------+
    10   

select extract('dayofweek',timestamptz '2022-10-16 16:22:10+08:00') as dayinweek;

 dayinweek  
+---------+
    0   

select extract('dayofweek',timestamptz '2022-10-11 16:22:10+08:00') as dayinweek;

 dayinweek  
+---------+
    2   

select extract('week',timestamptz '2022-10-16 16:22:10+08:00') as weekofyear;

   weekofyear  
+------------+
      41  

select experimental_strftime(now(),'%Y-%m-%d %H:%M:%S');

  experimental_strftime
-------------------------
  2023-03-08 16:14:31
(1 row)

select to_char(now()::timestamp);

           to_char
------------------------------
  2023-10-25 16:30:30.672578

select cast('2023-03-08 16:14:31' as timestamp);

  timestamp
--------------------------
  2023-03-08 16:14:31

select experimental_strptime('2023-10-26 13:44:01','%Y-%m-%d %H:%M:%S');

  experimental_strptime
--------------------------
  2023-10-26 21:44:01+08
(1 row)

select  now(), now() + interval '1s' as newnow;

           now()                       |          newnow
---------------------------------------+------------------------------
2022-10-11 15:47:23.380 +0800          | 2022-10-11 15:47:24.380 +0800

select now(), now() + interval '1 day' as newnow;

               now              |            newnow
--------------------------------+--------------------------------
  2022-10-18 11:06:25.522553+08 | 2022-10-19 11:06:25.522553+08

select (date_trunc('month',now()) + interval '1 month' + interval '-1 day')::DATE;

           date
--------------------------
       2022-10-31

select (date_trunc('month',now()) + interval '1 month' + interval '-1 second')::timestamp;

          date
--------------------------
  2022-10-31 23:59:59

select gen_random_uuid();

            gen_random_uuid             
+--------------------------------------+
  9c5b836c-486b-46ed-acb3-701c093f59fa  

select unique_rowid();

     unique_rowid     
+--------------------+
  514553266254184454  

select abs(-11.19);

   abs
---------
  11.19

select cbrt(27.0);

          cbrt
-------------------------
  3.0000000000000000000

select ceil(23.7);

  ceil
--------
   24

select ceiling(-95.3);

  ceiling
-----------
      -95

select degrees(0.5);

       degrees
---------------------
  28.64788975654116

select div(9,4);

  div
-------
    2

select exp(1.0);

           exp
-------------------------
  2.7182818284590452354

select floor(-42.8);

  floor
---------
    -43

select ln(2.0);

            ln
--------------------------
  0.69314718055994530942

select log(100.0);

           log
-------------------------
  2.0000000000000000000

select log(2.0, 64.0);

           log
-------------------------
  6.0000000000000000000

 select mod(8,3);

  mod
-------
    2

select pi();

         pi
---------------------
  3.141592653589793

select pow(2,3);

 pow
-------
    8

select random();

        random
----------------------
  0.7155664026177302

select round(10.7);

  round
---------
     11

select round(10.1);

  round
---------
     10

select round(10.3455,2);

  round
---------
  10.35

select trunc(-45.71);

  trunc
---------
    -45

select sind(45.0);

         sind
----------------------
  0.7071067811865475

select sinh(90.0);

          sinh
-------------------------
  6.102016471589204e+38

create table Player  
( 
PlayerName VARCHAR(100) NOT NULL,
RunScored INT NOT NULL,
WicketsTaken INT NOT NULL
);


insert into Player  
(PlayerName, RunScored,  WicketsTaken )
values
('KL Rahul', 52, 0 ),
('Hardik Pandya', 30, 1 ),
('Ravindra Jadeja', 18, 2 ),
('Washington Sundar', 10, 1),
('D Chahar', 11, 2 ),  
('Mitchell Starc', 0, 3);

select  STDDEV_POP(WicketsTaken) 
as Pop_Std_Dev_Wickets   
from Player ;

  pop_standard_deviation
--------------------------
   16.876183086099639597

select uuid_generate_v4();

            uuid_generate_v4
----------------------------------------
  165f8c68-d670-4708-aa50-ae6fc1bb72b0
(1 row)

create table Player  
( 
PlayerName VARCHAR(100) NOT NULL,
RunScored INT NOT NULL,
WicketsTaken INT NOT NULL
);


insert into Player  
(PlayerName, RunScored,  WicketsTaken )
values
('KL Rahul', 52, 0 ),
('Hardik Pandya', 30, 1 ),
('Ravindra Jadeja', 18, 2 ),
('Washington Sundar', 10, 1),
('D Chahar', 11, 2 ),  
('Mitchell Starc', 0, 3);

select  VAR_POP(RunScored ) as Run_POPVariance  
                          from Player ;

     run_popvariance
-------------------------
  284.80555555555555556

select  VAR_SAMP(RunScored ) as Run_Variance 
from Player ;

      run_variance
-------------------------
  341.76666666666666667

select timeofday();

               timeofday
----------------------------------------
  Fri Nov 4 11:33:43.240953 2022 +0800

select asind(1);

  asind
---------
     90
(1 row)

select acosd(0);

  acosd
---------
     90
(1 row)

create table book (id int,sal int ,bname string);

insert into book values (1, 1, '1984');
insert into book values (2, 1, 'Animal Farm');
insert into book values (3, 2, 'O Alquimista');
insert into book values (4, 2, 'Brida');

select every(sal < 10) from book;

  every
---------
  true
(1 row)

select  ascii('a');

  ascii  
+--------------+
   97

select initcap('hi THOMAS');

  initcap  
+--------------+
 Hi Thomas

select decode('MTIzAAE=', 'base64'); 

  decode  
+--------------+
   123  

select char_length('stfwwwwwfsf');

  char_length  
+--------------+
   11  

select chr(76);

  chr  
+--------------+
   L  

create table full_test(
first_name varchar(10),
last_name  varchar(10)
);

insert into full_test values ('zhang','sanfeng'),('liu','dehua'),('王','阳明');

select 
    CONCAT(first_name, ' ', last_name) AS "Full name"
from 
    full_test;

  Full name  
+--------------+
  zhang sanfeng
  liu dehua
    王 阳明 

select concat_ws('=', username, address) as info from user;

  Full name  
+--------------+
    张三=上海
    李四=上海
    王五=上海

select  lower('ADDRESS');

    lower
+--------------+
   address

select overlay('Txxxxas' placing 'hom' from 2 for 4) ;

   overlay 
+--------------+
   Thomas

select overlay('Txxxxas' placing 'hom' from 2 ) ;

   overlay  
+--------------+
   Thomxas

select  convert_from('text_in_utf8', 'UTF8');

  convert_from
----------------
  text_in_utf8

select convert_to('some text', 'UTF8');

   convert_to  
+--------------+
   some text

select encode(E'123\000\001', 'base64');

   encode  
+--------------+
   MTIzAAE=  

select  left('1234567890', 5);

  left
---------
  12345

select  upper('abcdef');

   upper  
+--------------+
   ABCDEF  

select  md5('hubble');

               md5  
+--------------------------------+
 90dc10ef0211b0088ac9430df0f6c158  

select quote_nullable(123);

quote_nullable  
+--------------+
     '123'  

select replace (
        'http://www.baidu.cn',
        'http',
        'https'
    );

    replace 
+--------------------+
 https://www.baidu.cn  

select sha256('hubble');

    sha256
+---------------------------------------------------------------+
 18d9486f99df32c58605e574ffae3773efe42294e95a1577c52a6f5d987ad44f 

select split_part('add.ress', '.', 1);

   split_part
+--------------------+
   add

select strpos('high', 'ig');

   strpos 
+--------------+
   2

select substr('address', 2,3);

   substr 
+--------------+
   ddr

select substr('address', 2);

   substr 
+--------------+
   ddress

select to_english(100);

 to_english 
+--------------+
 one-zero-zero

select translate('abcde', 'ad', '14');

   translate 
+--------------+
   1bc4e

DIFFERENCE(string, string)

select SOUNDEX('poor') soundex_poor, SOUNDEX('pour') soundex_pour, 
DIFFERENCE('poor', 'pour') similarity;

  soundex_poor | soundex_pour | similarity
---------------+--------------+-------------
  P600         | P600         | 4

select ename, 
       deptno, 
       sal,
       cume_dist() OVER (order by sal) as cume_dist
from (table emp order by sal desc limit 10);

   ename  | deptno |   sal    | cume_dist
----------+--------+----------+------------
    TH    |     30 |  4801.79 |       0.1
  FORD    |     20 |  4831.53 |       0.3
  SCOTT   |     20 |  4831.53 |       0.3
  MILLER  |     10 |  4936.76 |       0.4
  KING    |     10 |  5000.00 |       0.5
  PAIABRS |     20 |  5381.50 |       0.8
  PAIRS   |     10 |  5381.50 |       0.8
  LEAERS  |     40 |  5381.50 |       0.8
  LAIHUI  |     10 | 18000.00 |         1
   LUCK   |     10 | 18000.00 |         1

PERCENT_RANK() OVER (
    [partition by partition_expression, ... ]
    order by sort_expression [ASC | desc], ...
)

select
    ename,
    sal,
    PERCENT_RANK() OVER (
        order by sal
    )
from
    (table emp  order by sal desc limit 10);

   ename  |   sal    |    percent_rank
----------+----------+---------------------
  LUCK    |  4801.79 |                  0
  FORD    |  4831.53 | 0.1111111111111111
  SCOTT   |  4831.53 | 0.1111111111111111
  MILLER  |  4936.76 | 0.3333333333333333
  KING    |  5000.00 | 0.4444444444444444
  PAIABRS |  5381.50 | 0.5555555555555556
  PAIRS   |  5381.50 | 0.5555555555555556
  LEAERS  |  5381.50 | 0.5555555555555556
  LAIHUI  | 18000.00 | 0.8888888888888888
  WANG    | 18000.00 | 0.8888888888888888

NTILE(buckets) OVER (
    [partition by partition_expression, ... ]
    [order by sort_expression [ASC | desc], ...]
)

create table sales_stats(
    name  VARCHAR(100) NOT NULL,
    year  INT          NOT NULL CHECK (year > 0),
    amount DECIMAL(10, 2) CHECK (amount >= 0)
   
);

insert into 
    sales_stats(name, year, amount)
values
    ('Raju kumar', 2018, 120000),
    ('Alibaba', 2018, 110000),
    ('Gabbar Singh', 2018, 150000),
    ('Kadar Khan', 2018, 30000),
    ('Amrish Puri', 2018, 200000),
    ('Raju kumar', 2019, 150000),
    ('Alibaba', 2019, 130000),
    ('Gabbar Singh', 2019, 180000),
    ('Kadar Khan', 2019, 25000),
    ('Amrish Puri', 2019, 270000);

select 
    name,
    amount,
    NTILE(3) OVER(
        order by amount
    )
from
    sales_stats
WHERE
    year = 2018;

      name     |  amount   | ntile
---------------+-----------+--------
  Raju kumar   | 120000.00 |     2
  Alibaba      | 110000.00 |     1
  Gabbar Singh | 150000.00 |     2
  Kadar Khan   |  30000.00 |     1
  Amrish Puri  | 200000.00 |     3

select 
    name,
    amount,
    NTILE(3) OVER(
        partition by year
        order by amount
    )
from
    sales_stats;

      name     |  amount   | ntile
---------------+-----------+--------
  Raju kumar   | 120000.00 |     2
  Alibaba      | 110000.00 |     1
  Gabbar Singh | 150000.00 |     2
  Kadar Khan   |  30000.00 |     1
  Amrish Puri  | 200000.00 |     3
  Raju kumar   | 150000.00 |     2
  Alibaba      | 130000.00 |     1
  Gabbar Singh | 180000.00 |     2
  Kadar Khan   |  25000.00 |     1
  Amrish Puri  | 270000.00 |     3

FIRST_VALUE ( expression )  
OVER ( 
    [partition by partition_expression, ... ]
    order by sort_expression [ASC | desc], ...
)

create table product_groups (
    group_id int PRIMARY KEY,
    group_name VARCHAR(255) NOT NULL
);

create table products (
    product_name VARCHAR (255) NOT NULL,
    price DECIMAL (11, 2),
    group_id INT NOT NULL
);

insert into product_groups (group_name)
values
    (1,'Smartphone'),
    (2,'Laptop'),
    (3,'Tablet');

insert into products (product_name, group_id, price)
values
    ('Microsoft Lumia', 1, 200),
    ('HTC One', 1, 400),
    ('Nexus', 1, 500),
    ('iPhone', 1, 900),
    ('HP Elite', 2, 1200),
    ('Lenovo Thinkpad', 2, 700),
    ('Sony VAIO', 2, 700),
    ('Dell Vostro', 2, 800),
    ('iPad', 3, 700),
    ('Kindle Fire', 3, 150),
    ('Samsung Galaxy Tab', 3, 200);

select 
    product_name,
    group_id,
    price,
    FIRST_VALUE(product_name) 
    OVER(
        order by price
    ) lowest_price
from 
    products;

     product_name    | group_id |  price  | lowest_price
---------------------+----------+---------+---------------
  Microsoft Lumia    |        1 |  200.00 | Kindle Fire
  HTC One            |        1 |  400.00 | Kindle Fire
  Nexus              |        1 |  500.00 | Kindle Fire
  iPhone             |        1 |  900.00 | Kindle Fire
  HP Elite           |        2 | 1200.00 | Kindle Fire
  Lenovo Thinkpad    |        2 |  700.00 | Kindle Fire
  Sony VAIO          |        2 |  700.00 | Kindle Fire
  Dell Vostro        |        2 |  800.00 | Kindle Fire
  iPad               |        3 |  700.00 | Kindle Fire
  Kindle Fire        |        3 |  150.00 | Kindle Fire
  Samsung Galaxy Tab |        3 |  200.00 | Kindle Fire

select 
    product_name,
    group_id,
    price,
    FIRST_VALUE(product_name) 
    OVER(
    partition by group_id
        order by price
        RANGE between 
            UNBOUNDED PRECEDING and 
            UNBOUNDED FOLLOWING
    ) lowest_price
from 
    products;

     product_name    | group_id |  price  |  lowest_price
---------------------+----------+---------+------------------
  Microsoft Lumia    |        1 |  200.00 | Microsoft Lumia
  HTC One            |        1 |  400.00 | Microsoft Lumia
  Nexus              |        1 |  500.00 | Microsoft Lumia
  iPhone             |        1 |  900.00 | Microsoft Lumia
  HP Elite           |        2 | 1200.00 | Lenovo Thinkpad
  Lenovo Thinkpad    |        2 |  700.00 | Lenovo Thinkpad
  Sony VAIO          |        2 |  700.00 | Lenovo Thinkpad
  Dell Vostro        |        2 |  800.00 | Lenovo Thinkpad
  iPad               |        3 |  700.00 | Kindle Fire
  Kindle Fire        |        3 |  150.00 | Kindle Fire
  Samsung Galaxy Tab |        3 |  200.00 | Kindle Fire

LAST_VALUE ( expression )  
OVER ( 
    [partition by partition_expression, ... ]
    order by sort_expression [ASC | desc], ...
)

select 
    product_name,
    price,
    LAST_VALUE(product_name) 
    OVER(
        order by price
        RANGE between 
            UNBOUNDED PRECEDING and 
            UNBOUNDED FOLLOWING
    ) highest_price
from 
    products;

     product_name    |  price  | highest_price
---------------------+---------+----------------
  Microsoft Lumia    |  200.00 | HP Elite
  HTC One            |  400.00 | HP Elite
  Nexus              |  500.00 | HP Elite
  iPhone             |  900.00 | HP Elite
  HP Elite           | 1200.00 | HP Elite
  Lenovo Thinkpad    |  700.00 | HP Elite
  Sony VAIO          |  700.00 | HP Elite
  Dell Vostro        |  800.00 | HP Elite
  iPad               |  700.00 | HP Elite
  Kindle Fire        |  150.00 | HP Elite
  Samsung Galaxy Tab |  200.00 | HP Elite

select 
    product_name,
    group_id,
    price,
    LAST_VALUE(product_name) 
    OVER(
    partition by group_id
        order by price
        RANGE between 
            UNBOUNDED PRECEDING and 
            UNBOUNDED FOLLOWING
    ) highest_price
from 
    products;

     product_name    | group_id |  price  | highest_price
---------------------+----------+---------+----------------
  Microsoft Lumia    |        1 |  200.00 | iPhone
  HTC One            |        1 |  400.00 | iPhone
  Nexus              |        1 |  500.00 | iPhone
  iPhone             |        1 |  900.00 | iPhone
  HP Elite           |        2 | 1200.00 | HP Elite
  Lenovo Thinkpad    |        2 |  700.00 | HP Elite
  Sony VAIO          |        2 |  700.00 | HP Elite
  Dell Vostro        |        2 |  800.00 | HP Elite
  iPad               |        3 |  700.00 | iPad
  Kindle Fire        |        3 |  150.00 | iPad
  Samsung Galaxy Tab |        3 |  200.00 | iPad

 ARRAY_AGG(expression [order by [sort_expression {ASC | desc}], [...])

create table film (id int,pname  string,fname string);
insert into film values (1,'战狼二','wujing');
insert into film values (1,'断背山','lian');
insert into film values (3,'西游记','wuchengen');;
insert into film values (3,'水浒传','shinaian');
insert into film values (2,'亮剑','liyunlong');
insert into film values (2,'五号特工组','wanglikun');

select
    id,
    ARRAY_AGG (pname || ' ' || fname) actors
from
    film 
group by id;

 id  |                actors
-----+----------------------------------------
   1 | {战狼二 wujing,断背山 lian}
   3 | {西游记 wuchengen,水浒传 shinaian}
   2 | {亮剑 liyunlong,五号特工组 wanglikun}

LEAD(expression [, offset [, default_value]]) 
OVER (
    [partition by partition_expression, ... ]
    order by sort_expression [ASC | desc], ...
)

create table sales(
    year INT CHECK(year > 0),
    group_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY(year, group_id)
);

insert into 
    sales(year, group_id, amount) 
values
    (2018, 1, 1474),
    (2018, 2, 1787),
    (2018, 3, 1760),
    (2019, 1, 1915),
    (2019, 2, 1911),
    (2019, 3, 1118),
    (2020, 1, 1646),
    (2020, 2, 1975),
    (2020, 3, 1516);

WITH cte AS (
    select 
        year, 
        SUM(amount) amount
    from sales
    group by year
    order by year
) 
select
    year, 
    amount,
    LEAD(amount, 1) OVER (
        order by year
    ) next_year_sales
from
    cte;

  year | amount  | next_year_sales
-------+---------+------------------
  2018 | 5021.00 |         4944.00
  2019 | 4944.00 |         5137.00
  2020 | 5137.00 |         NULL

select
    year, 
    amount,
    group_id,
    LEAD(amount, 1) OVER (
        partition by group_id
        order by year
    ) next_year_sales
from
    sales;

  year | amount  | group_id | next_year_sales
-------+---------+----------+------------------
  2018 | 1474.00 |        1 |         1915.00
  2019 | 1915.00 |        1 |         1646.00
  2020 | 1646.00 |        1 |         NULL
  2018 | 1787.00 |        2 |         1911.00
  2019 | 1911.00 |        2 |         1975.00
  2020 | 1975.00 |        2 |         NULL
  2018 | 1760.00 |        3 |         1118.00
  2019 | 1118.00 |        3 |         1516.00
  2020 | 1516.00 |        3 |         NULL

LAG(expression [, offset [, default_value]]) 
OVER (
    [partition by partition_expression, ... ]
    order by sort_expression [ASC | desc], ...
)

create table sales(
    year INT CHECK(year > 0),
    group_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY(year, group_id)
);

insert into   
    sales(year, group_id, amount) 
values
    (2018, 1, 1474),
    (2018, 2, 1787),
    (2018, 3, 1760),
    (2019, 1, 1915),
    (2019, 2, 1911),
    (2019, 3, 1118),
    (2020, 1, 1646),
    (2020, 2, 1975),
    (2020, 3, 1516);

WITH cte AS (
    select 
        year, 
        SUM(amount) amount
    from sales
    group by year
    order by year
) 
select
    year, 
    amount,
    LAG(amount, 1) OVER (
        order by year
    ) previous_year_sales
from
    cte;

  year | amount  | previous_year_sales
-------+---------+----------------------
  2018 | 5021.00 |             NULL
  2019 | 4944.00 |             5021.00
  2020 | 5137.00 |             4944.00

select
    year, 
    amount,
    group_id,
    LAG(amount, 1) OVER (
        partition by group_id
        order by year
    ) previous_year_sales
from
    sales;

  year | amount  | group_id | previous_year_sales
-------+---------+----------+----------------------
  2018 | 1474.00 |        1 |             NULL
  2019 | 1915.00 |        1 |             1474.00
  2020 | 1646.00 |        1 |             1915.00
  2018 | 1787.00 |        2 |             NULL
  2019 | 1911.00 |        2 |             1787.00
  2020 | 1975.00 |        2 |             1911.00
  2018 | 1760.00 |        3 |             NULL
  2019 | 1118.00 |        3 |             1760.00
  2020 | 1516.00 |        3 |             1118.00

NTH_VALUE(expression, offset) 
OVER (
    [partition by partition_expression]
    [ order by sort_expression [ASC | desc]
    frame_clause ]
)

create table products (
    product_name VARCHAR (255) NOT NULL,
    price DECIMAL (11, 2),
    group_id INT NOT NULL
);

insert into products (product_name, group_id, price)
values
    ('Microsoft Lumia', 1, 200),
    ('HTC One', 1, 400),
    ('Nexus', 1, 500),
    ('iPhone', 1, 900),
    ('HP Elite', 2, 1200),
    ('Lenovo Thinkpad', 2, 700),
    ('Sony VAIO', 2, 700),
    ('Dell Vostro', 2, 800),
    ('iPad', 3, 700),
    ('Kindle Fire', 3, 150),
    ('Samsung Galaxy Tab', 3, 200);

select 
    product_name,
    price,
    NTH_VALUE(product_name, 3) 
    OVER(
        order by price desc
        RANGE between 
            UNBOUNDED PRECEDING and 
            UNBOUNDED FOLLOWING
    )
from 
    products;

     product_name    |  price  |  nth_value
---------------------+---------+--------------
  Microsoft Lumia    |  200.00 | Dell Vostro
  HTC One            |  400.00 | Dell Vostro
  Nexus              |  500.00 | Dell Vostro
  iPhone             |  900.00 | Dell Vostro
  HP Elite           | 1200.00 | Dell Vostro
  Lenovo Thinkpad    |  700.00 | Dell Vostro
  Sony VAIO          |  700.00 | Dell Vostro
  Dell Vostro        |  800.00 | Dell Vostro
  iPad               |  700.00 | Dell Vostro
  Kindle Fire        |  150.00 | Dell Vostro
  Samsung Galaxy Tab |  200.00 | Dell Vostro

select 
    product_name,
    price,
    group_id,
    NTH_VALUE(product_name, 1) 
    OVER(
        partition by group_id
        order by price desc
        RANGE between 
            UNBOUNDED PRECEDING and 
            UNBOUNDED FOLLOWING
    )
from 
    products;

     product_name    |  price  | group_id | nth_value
---------------------+---------+----------+------------
  Microsoft Lumia    |  200.00 |        1 | iPhone
  HTC One            |  400.00 |        1 | iPhone
  Nexus              |  500.00 |        1 | iPhone
  iPhone             |  900.00 |        1 | iPhone
  HP Elite           | 1200.00 |        2 | HP Elite
  Lenovo Thinkpad    |  700.00 |        2 | HP Elite
  Sony VAIO          |  700.00 |        2 | HP Elite
  Dell Vostro        |  800.00 |        2 | HP Elite
  iPad               |  700.00 |        3 | iPad
  Kindle Fire        |  150.00 |        3 | iPad
  Samsung Galaxy Tab |  200.00 |        3 | iPad

AVG(column)

select avg(sal) from emp;

           avg
-------------------------
  4655.8677777777777778

select deptno,avg(sal) from emp group by deptno;

  deptno |          avg
---------+------------------------
      10 | 7373.4033333333333333
      20 |              3490.455
      40 |               5381.50
      30 | 3010.2916666666666667
      50 |               1000.00

select deptno,avg(sal) from emp group by deptno having(avg(sal))>5000;

  deptno |          avg
---------+------------------------
      10 | 7373.4033333333333333
      40 |               5381.50

All rows including null and Duplicates

All rows except null.

All rows without null and Duplicates

select count(*) from emp;

  count
---------
     27

select count(DISTINCT deptno) from emp;

  count
---------
     5

SUM(column)

select deptno,sum(sal) from emp group by deptno ;

  deptno |   sum
---------+-----------
      10 | 66360.63
      20 | 34904.55
      40 |  5381.50
      30 | 18061.75
      50 |  1000.00

select sum(sal) from emp;

     sum
-------------
  125708.43

MAX(expression);

select max(sal) from emp;

    max
------------
  18000.00

select deptno,max(sal) from emp group by deptno;

  deptno |   max
---------+-----------
      10 | 18000.00
      20 |  5381.50
      40 |  5381.50
      30 |  4801.79
      50 |  1000.00

MIN(expression);

select min(sal) from emp;

    min
------------
   1000.00

select deptno,min(sal) from emp group by deptno;

 deptno  |   min
---------+----------
      10 | 2134.00
      20 | 1000.00
      40 | 5381.50
      30 | 2165.00
      50 | 1000.00

select to_json('json type'::text);

    to_json
---------------
  "json type"

select to_jsonb('json type'::text);

    to_jsonb
---------------
  "json type"

select json_array_length('[1,2,3,4]');

  json_array_length
---------------------
                  4

 select json_build_array(1,2,'3',4,5);

  json_build_array
---------------------
  [1, 2, "3", 4, 5]

select json_build_object('1','2','3','4');

   json_build_object
------------------------
  {"1": "2", "3": "4"}

create table reports (rep_id int primary key, data json);


insert into reports (rep_id, data)
values
(1, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 2, "src":"barB.png", "pos": "top"}], "background":"background.png"}')
, (2, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 2, "src":"barC.png", "pos": "top"}], "background":"bacakground.png"}')
, (3, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "middle"},{"album": 2, "src":"barB.png", "pos": "middle"}],"background":"background.png"}')
, (4, '{"objects":[{"album": 1, "src":"fooA.png", "pos": "top"}, {"album": 3, "src":"barB.png", "pos": "top"}], "background":"backgroundA.png"}')
;

select jsonb_pretty( '{"name": "Alice", "agent": {"bot": true} }'::jsonb );

     jsonb_pretty
-----------------------
  {
      "agent": {
          "bot": true
      },
      "name": "Alice"
  }

select * from json_object_keys('{"b":"1","a":"2"}');

  json_object_keys
--------------------
  a
  b

select jsonb_set('{"name":"bob","age":"27"}'::jsonb,'{age}','"28"'::jsonb,false); 

           jsonb_set
--------------------------------
  {"age": "28", "name": "bob"}

select * from json_each('{"b":"1","a":"2"}'); 

  key | value
------+--------
  a   | "2"
  b   | "1"

select * from json_each_text('{"b":"1","a":"2"}');

  key | value
------+--------
  a   | 2
  b   | 1

select * from json_object_keys('{"b":"1","a":"2"}');

  json_object_keys
--------------------
  a
  b

select * from json_typeof('{"b":"1","a":"2"}');

  json_typeof
---------------
  object

select '{"b":"1","a":"2"}'::json - 'a';

   column
--------------
  {"b": "1"}

select '["a","b","c"]'::jsonb - 0; 

  column  
------------
 ["b", "c"]

select '["a","b","c"]'::jsonb - 1; 

  column  
------------
 ["a", "c"]

select json_strip_nulls('[{"f1":1,"f2":null},2,null,3]');

     json_strip_nulls
---------------------------
  [{"f1": 1}, 2, null, 3]