SQL GROUP BY and kind of rereduce afterwards

user_2030095 0
Viewed 47

I'm using PostgreSQL v.11. I have a table with 3 columns. My goal is to find redundancy inside data.

First of all, I do a simple GROUP BY:

SELECT client, block, "date"
FROM lines
GROUP BY client, block, "date"
ORDER BY client, block

The result looks like this:

1 | P10001 | 2020-01-01
1 | P10002 | 2020-04-17
1 | P10002 | 2020-05-04
1 | P10003 | 2020-05-05

Now I would like to identify rows that have the same "block", but different "date". In this example, this is the case for row 2 and 3 (block = P10002)

For now, I use javascript to parse the complete resultset and find these 2 redundant rows (I use result.reduce(...))

But is there a way, in pure SQL, to extract this "rereduced" result?

The expected result is this:

1 | P10002 | 2020-04-17
1 | P10002 | 2020-05-04

It should be great to keep the two dates in the resultset, because I need to change the "block" for only one of them. If I don't have the date column in the resultset, I will need to do a second request to find all the affected rows.

Here is a script for the table and data

CREATE TABLE lines (
  "client" integer NOT NULL,
  "block" text NOT NULL,
  "date" date NOT NULL
);

INSERT INTO lines ("client", "block", "date") VALUES
  (1, 'P10001', '2020-01-01'),
  (1, 'P10002', '2020-04-17'),
  (1, 'P10002', '2020-05-04'),
  (1, 'P10003', '2020-05-05');

Many thanks

reduce group-by postgresql sql
4 Answers

You can use HAVING clause along with GROUPing BY block as distinctly counting the dates :

SELECT block
  FROM lines
 GROUP BY block
HAVING COUNT( DISTINCT "date" ) > 1;


block
------
P10002
user_58413060

You should use the HAVINGclause to get the redundant datasets. I assume you need the client as well in the redundancy detection.

The query looks like this.

SELECT client, block
FROM lines
GROUP BY client, block
HAVING count(distinct "date") > 1
ORDER BY client, block
user_100103660

You can do it with EXISTS:

select l.* from lines l
where exists (
  select 1 from lines
  where client = l.client and block = l.block and date <> l.date
)

If there is no case of duplicate dates for each client, block you could also use COUNT(*) window function:

select client, block, date
from (
  select *, count(*) over (partition by client, block) counter
  from lines
) t
where counter > 1

See the demo.
Results:

> client | block  | date      
> -----: | :----- | :---------
>      1 | P10002 | 2020-04-17
>      1 | P10002 | 2020-05-04
user_104988280

This works in all database management systems.

Some DBMSs (you don't specify) also offer GROUP BY ROLLUP(). Look that up in your DBMS's docu.

As said, this one works everywhere - they might sort NULLs differently.

WITH
lines(client,block,dt) AS (
          SELECT 1,'P10001',DATE '2020-01-01'
UNION ALL SELECT 1,'P10002',DATE '2020-04-17'
UNION ALL SELECT 1,'P10002',DATE '2020-05-04'
UNION ALL SELECT 1,'P10003',DATE '2020-05-05'
UNION ALL SELECT 1,'P10001',DATE '2020-01-01'
UNION ALL SELECT 1,'P10002',DATE '2020-04-17'
UNION ALL SELECT 1,'P10002',DATE '2020-05-04'
UNION ALL SELECT 1,'P10003',DATE '2020-05-05'
)
SELECT
  client
, block
, dt
FROM lines
UNION ALL
SELECT
  client
, block
, NULL::DATE AS dt
FROM lines
GROUP BY
  client
, block
ORDER BY
  client
, block
, dt
-- out  client | block  |     dt     
-- out --------+--------+------------
-- out       1 | P10001 | 
-- out       1 | P10001 | 2020-01-01
-- out       1 | P10001 | 2020-01-01
-- out       1 | P10002 | 
-- out       1 | P10002 | 2020-04-17
-- out       1 | P10002 | 2020-04-17
-- out       1 | P10002 | 2020-05-04
-- out       1 | P10002 | 2020-05-04
-- out       1 | P10003 | 
-- out       1 | P10003 | 2020-05-05
-- out       1 | P10003 | 2020-05-05
user_67882550
Related