CODEWITHSUNDEEP
sql-serveroracle-databaseoracle11ganalyticswindow-functions
Rafael Angarita
Rafael Angarita

Reputation: 787

LAG of MIN in SQL Analytic

I have a table containing employees id, year id, client id, and the number of sales. For example:

 --------------------------------------
  id_emp | id_year | sales | client id
 --------------------------------------
    4    |    1    |   14  |    1
    4    |    1    |   10  |    2
    4    |    2    |   11  |    1
    4    |    2    |   17  |    2

For a employee, I want to obtain rows with the minimum sales per year and the minimum sales of the previous year.

One of the queries I tried is the following:

select distinct 
  id_emp,
  id_year, 
  MIN(sales) OVER(partition by id_emp, id_year) AS min_sales,
  LAG(min(sales), 1) OVER(PARTITION BY id_emp, id_year
                              ORDER BY id_emp, id_year) AS previous
 from facts
where id_emp = 4
group by id_emp, id_year, sales;

I get the result:

 -------------------------------------    
  id_emp | id_year | sales | previous
 -------------------------------------
    4    |    1    |   10  |  (null)
    4    |    1    |   10  |    10
    4    |    2    |   11  |  (null)

but I expect to get:

 -------------------------------------
  id_emp | id_year | sales | previous
 -------------------------------------
    4    |    1    |   10  |  (null)
    4    |    2    |   11  |    10

Upvotes: 1

Views: 1713

Answers (4)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

You mean like this?

select id_emp, id_year, min(sales) as min_sales,
    lag(min(sales)) over (partition by id_emp order by id_year) as prev_year_min_sales
from facts
where id_emp = 4
group by id_emp, id_year;

Upvotes: 2

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

You could get your desired output using ROW_NUMBER() and LAG() analytic functions.

For example,

Table

SQL> SELECT * FROM t;

    ID_EMP    ID_YEAR      SALES  CLIENT_ID
---------- ---------- ---------- ----------
         4          1         14          1
         4          1         10          2
         4          2         11          1
         4          2         17          2

Query

SQL> WITH DATA AS
  2    (SELECT t.*,
  3      row_number() OVER(PARTITION BY id_emp, id_year ORDER BY sales) rn
  4    FROM t
  5    )
  6  SELECT id_emp,
  7    id_year ,
  8    sales ,
  9    lag(sales) over(order by sales) previous
 10  FROM DATA
 11  WHERE rn =1;

    ID_EMP    ID_YEAR      SALES   PREVIOUS
---------- ---------- ---------- ----------
         4          1         10
         4          2         11         10

Upvotes: 0

Fnaxiom
Fnaxiom

Reputation: 396

I believe it is because you are using sales column in your group by statement. Try to remove it and just use 

  GROUP BY id_emp,id_year

Upvotes: 0

MT0
MT0

Reputation: 168051

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE EMPLOYEE_SALES ( id_emp, id_year, sales, client_id ) AS
          SELECT 4, 1, 14, 1 FROM DUAL
UNION ALL SELECT 4, 1, 10, 2 FROM DUAL
UNION ALL SELECT 4, 2, 11, 1 FROM DUAL
UNION ALL SELECT 4, 2, 17, 2 FROM DUAL;

Query 1:

SELECT ID_EMP,
       ID_YEAR,
       SALES AS SALES,
       LAG( SALES ) OVER ( PARTITION BY ID_EMP ORDER BY ID_YEAR ) AS PREVIOUS
FROM   (
  SELECT e.*,
         ROW_NUMBER() OVER ( PARTITION BY id_emp, id_year ORDER BY sales ) AS RN
  FROM   EMPLOYEE_SALES e
)
WHERE rn = 1

Query 2:

SELECT ID_EMP,
       ID_YEAR,
       MIN( SALES ) AS SALES,
       LAG( MIN( SALES ) ) OVER ( PARTITION BY ID_EMP ORDER BY ID_YEAR ) AS PREVIOUS
FROM   EMPLOYEE_SALES
GROUP BY ID_EMP, ID_YEAR

Results - Both give the same output:

| ID_EMP | ID_YEAR | SALES | PREVIOUS |
|--------|---------|-------|----------|
|      4 |       1 |    10 |   (null) |
|      4 |       2 |    11 |       10 |

Upvotes: 2

Related Questions