PostgreSQL - funciones ventas ejemplos

drop table if exists empsalary

CREATE TABLE empsalary (

  empno BIGSERIAL PRIMARY KEY,

  depname TEXT,

  location TEXT,

  salary DECIMAL,

  old integer

);

INSERT INTO empsalary VALUES (1, 'develop', 'fi', 5200,24);

INSERT INTO empsalary VALUES (2, 'develop', 'fi', 4200,26);

INSERT INTO empsalary VALUES (3, 'develop', 'fi', 4500,35);

INSERT INTO empsalary VALUES (4, 'develop', 'fi', 6000,42);

INSERT INTO empsalary VALUES (5, 'develop', 'se', 5200,44);

INSERT INTO empsalary VALUES (6, 'personnel', 'fi', 3500,21);

INSERT INTO empsalary VALUES (7, 'personnel', 'fi', 3900,18);

INSERT INTO empsalary VALUES (8, 'sales', 'se', 4800,52);

INSERT INTO empsalary VALUES (9, 'sales', 'se', 5000,34);

INSERT INTO empsalary VALUES (10, 'sales', 'se', 4800,30);

INSERT INTO empsalary VALUES (11,'sales', 'se',3000,24);

INSERT INTO empsalary VALUES (12,'sales', 'se',3200,26);

INSERT INTO empsalary VALUES (13,'sales', 'se',3500,35);

INSERT INTO empsalary VALUES (14,'mkt','fl',2000,22);

INSERT INTO empsalary VALUES (15,'mkt','fl',2100,42);

INSERT INTO empsalary VALUES (16,'mkt','fl',2400,40);

INSERT INTO empsalary VALUES (17,'production','ny',2800,41);

INSERT INTO empsalary VALUES (18,'production','ny',2400,29);

INSERT INTO empsalary VALUES (19,'production','ny',1900,19);

INSERT INTO empsalary VALUES (20,'production','ny',3600,45);

INSERT INTO empsalary VALUES (21,'production','ny',3000,40);

---

SELECT

  empno,

  depname,

  salary,

  old

FROM empsalary;

--Sin ventana

SELECT  depname,

   avg(salary)

FROM empsalary group by depname;

--promedio salary por depname usando over

SELECT

  empno,

  depname,

  salary,

  old,

  avg(salary) OVER (PARTITION BY depname) AS salary_medio

FROM empsalary ;

--promedio salary por depname usando window

SELECT

  empno,

  depname,

  salary,

  old,

  avg(salary) OVER ventana_depname AS salary_medio

FROM empsalary

WINDOW

  ventana_depname AS (PARTITION BY depname);

-- otra prueba depname rango de sueldo

SELECT   empno,

  depname,

  salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary ;

--- dos promedios

SELECT

  empno,

  depname,

  salary,

  old,

  round(avg(salary) OVER ventana_depname) AS sal_medio,

  round(avg(old) OVER ventana_depname) AS ed_media

FROM empsalary

WINDOW

  ventana_depname AS (PARTITION BY depname);

--otro doble

SELECT salary, sum(salary) OVER (PARTITION BY depname) , avg(salary)  OVER (PARTITION BY depname) ::integer FROM empsalary;

--- diferencia salary

SELECT

  empno,

  depname,

  salary,

  old,

  salary - round(avg(salary) OVER ventana_depname) AS sal_diff,

  old - round(avg(old) OVER ventana_depname) AS ed_diff

FROM empsalary

WINDOW

  ventana_depname AS (PARTITION BY depname);

-- promedio sin particion

SELECT

  empno,

  depname,

  salary,

  old,

  round(avg(salary) OVER ventana_empresa) AS sal_medio,

  round(avg(old) OVER ventana_empresa) AS ed_media

FROM empsalary

WINDOW

  ventana_empresa AS ();

SELECT

  empno,

  depname,

  salary,

  old,

  salary - round(avg(salary) OVER ventana_empresa) AS sal_diff,

  old - round(avg(old) OVER ventana_empresa) AS ed_diff

FROM empsalary

WINDOW

  ventana_empresa AS ();

--- rank

SELECT

  empno,

  depname,

  salary,

  old,

  dense_rank() OVER ventana_depname_salary AS sal_pos,

  dense_rank() OVER ventana_depname_old AS ed_pos

FROM empsalary

WINDOW

  ventana_depname_salary AS

  (PARTITION BY depname ORDER BY salary DESC),

  ventana_depname_old AS

  (PARTITION BY depname ORDER BY old DESC);

--rank por produccion

SELECT

  empno,

  depname,

  salary,

  old,

  dense_rank() OVER ventana_depname_salary AS sal_pos,

  dense_rank() OVER ventana_depname_old AS ed_pos

FROM empsalary

WHERE

  depname = 'produccion'

WINDOW

  ventana_depname_salary AS

  (PARTITION BY depname ORDER BY salary DESC),

  ventana_depname_old AS

  (PARTITION BY depname ORDER BY old DESC);

--usar subselect

SELECT *

FROM

(

 SELECT

   empno,

   depname,

   salary,

   old,

   dense_rank() OVER ventana_depname_salary AS sal_pos,

   dense_rank() OVER ventana_depname_old AS ed_pos

 FROM empsalary

 WINDOW

   ventana_depname_salary AS

   (PARTITION BY depname ORDER BY salary DESC),

   ventana_depname_old AS

   (PARTITION BY depname ORDER BY old DESC)

) AS subselect

WHERE

  depname = 'produccion';

---otras pruebas numero de fila

select   empno,row_number() over()  from empsalary

select   row_number() over(order by empno) as columna,empno  from empsalary

---

SELECT orderid,totalamount, AVG(totalamount) OVER () as val_avg

FROM orders  where customerid= 101136;

SELECT orderid,totalamount, AVG(totalamount) OVER (PARTITION BY left(orderid::varchar,3) ORDER BY totalamount) as totalamount_avg

FROM orders  where customerid= 101136;

SELECT orderid,orderdate,totalamount, AVG(totalamount) OVER (PARTITION BY orderdate  ORDER BY orderdate) as totalamount_avg

FROM orders  where customerid= 101136;

select * from orders where customerid= 101136;

--Si se necesita filtrar o agrupar las filas después de terminar el cálculo de la ventana, se puede usar una subselección. Por ejemplo:

SELECT depname, empno, salary, old

FROM

  (SELECT depname, empno, salary, old,

          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos

     FROM empsalary

  ) AS ss

WHERE pos < 3;

--dado lo anterior obtener una lista con los mejores sueldos

SELECT depname, empno, salary, old

FROM

  (SELECT depname, empno, salary, old,

          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos

     FROM empsalary

  ) AS ss

WHERE pos =1;

(

SELECT depname, empno, salary, old,

          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos

     FROM empsalary

)  order by pos;

---

SELECT sum(salary) OVER w, avg(salary) OVER w

  FROM empsalary

  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

----

SELECT depname, location, empno, salary,

AVG(salary) OVER (PARTITION BY depname) avgdept,

SUM(salary) OVER (PARTITION BY depname) sumdept,

AVG(salary) OVER (PARTITION BY location) avgloc,

RANK() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos

FROM empsalary;