New to CTE – How to do a count after making table unique

cteredshift

I'm new to CTE's and usually sub query my SQL. I'm unsure how to get the results I want as I keep finding solutions that would require sub queries.

I have the below table in SQL. I'm trying to write a CTE to give me one line of data per day for each person. Where a person has more than one location ('where' field) for a day – I need to exclude all lines for that day. If a person has multiple lines for one day but the location is the same place – I want to only keep one line of this data.

This is an example of the original table:

This is an example of the result that I'm looking for (note – I don't need the ID field in the result).

Below is the definition and insert statement to the original dataset:

CREATE TABLE Tbl1
(`ID` int, `Person` varchar(5), `Date` varchar(10), `Where` varchar(6));

INSERT INTO Tbl1
(`ID`, `Person`, `Date`, `Where`)
VALUES
(1, 'Laura', '17/10/2019', 'School'),
(2, 'Laura', '18/10/2019', 'School'),
(3, 'Laura', '19/10/2019', 'School'),
(4, 'Laura', '19/10/2019', 'Park'),
(5, 'Laura', '20/10/2019', 'Home'),
(6, 'Laura', '21/10/2019', 'School'),
(7, 'Laura', '21/10/2019', 'School'),
(8, 'Laura', '21/10/2019', 'Home'),
(9, 'Tom', '17/10/2019', 'Home'),
(10, 'Tom', '18/10/2019', 'Home'),
(11, 'Tom', '18/10/2019', 'Home'),
(12, 'Tom', '19/10/2019', 'Home'),
(13, 'Tom', '19/10/2019', 'School'),
(14, 'Tom', '20/10/2019', 'Home');

Best Answer

Don't know why you need a CTE (or a subselect), wouldn't this simply be

select person, date_, max(where_) from tbl1
group by person, date_
having count(distinct where_) < 2

(fiddle)

PS. Note I changed the table definition to 1) remove backticks that are only used by MySQL to quote identifiers (Redshift is based on Postgres) and 2) rename columns with names that conflict with SQL keywords.

Related Solutions

MySQL CTE vs Join – Which to Use?

Without table structure, it's not easy to write code but here is a simplified query:

SELECT 
    c.*, i.*,
    r.max_date
FROM 
    customer AS c
  LEFT JOIN                                        -- or just JOIN 
    ( SELECT customernumber, 
             MAX(revenue_date) AS max_date
      FROM revenue
      GROUP BY customernumber
    ) AS r
      ON  r.customernumber = c.customernumber
  LEFT JOIN                                        -- or just JOIN
    invoice AS i
      ON  i.customernumber = c.customernumber
      AND i.invoice_date >= r.max_date ;

The above is standard SQL and will work in MySQL as in all other DBMS. For more complex situations though, where LATERAL or similar syntax can be used to provide more elegant (or more efficient) SQL, MySQL is not helping. It lacks many of the standard SQL syntax (like common table expressions, table value constructors, window functions, LATERAL, CHECK constraints and probably some other I forget).

If you want to simulate (SQL-Server's) OUTER APPLY, (the ISO/ANSI) LATERAL or (DB2's) TABLE syntax, it becomes (bad news) quite complicated but (good news) usually quite efficient with proper indexing. This is mainly useful when you have a small "base" table (the customer in this case) or you want to limit the query to only a few customers with a WHERE clause. While the query above with the GROUP BY will have to find the MAX(revenue_date) for all customers (and that means a full table or a full index scan in revenue), the following one will only find the max for the few selected customers (so a few index seeks):

SELECT 
    c.*, i.*,
    r.revenue_date AS max_date
FROM 
    customer AS c
  LEFT JOIN
    revenue AS r
      ON  r.customernumber = .customernumber
      AND r.PK = 
          ( SELECT ri.PK
            FROM revenue AS ri
            WHERE ri.customernumber = c.customernumber
            ORDER BY ri.revenue_date DESC
              LIMIT 1
          ) 
  LEFT JOIN                                        
    invoice AS i
      ON  i.customernumber = c.customernumber
      AND i.invoice_date >= r.revenue_date 
WHERE
    <some conditions on the customer table> ;

An advantage is that you can have any column of the revenue table in the final SELECT list.
There are several improvements/variations, like if the (customernumber, revenue_date) is unique, you can use the revenue_date instead of the primary key for the join.
If you don't want one row only but several (a TOP n query), it gets quite complicated but can still be done.

PostgreSQL Data Versioning – Updating Table of Versioned Rows with Historical Records

1st case

You seem to forget the valid_during range. As your third case suggests, there can be multiple entries per (rec_id, val), so you must select the right one:

UPDATE master m
SET    valid_on = f_array_sort(m.valid_on || u.valid_on) -- sorted array, see below
FROM   updates u
WHERE  m.rec_id = u.rec_id 
AND    m.valid_during @> u.valid_on  -- additional check
AND    m.val = u.val 
AND    NOT m.valid_on @> ARRAY[u.valid_on];

I assume the whole possible date range is always covered for each existing rec_id and valid_during shall not overlap per rec_id, or you'd have to do more.

After installing the additional module btree_gist, add an exclusion constraint to rule out overlapping date ranges if you don't have one, yet:

ALTER TABLE master ADD CONSTRAINT EXCLUDE
USING gist (rec_id WITH =, valid_during WITH &&)  -- disallow overlap

The GiST index this is implemented with is also a perfect match for the query. Details:

2nd / 3rd case

Assuming that every date range starts with the smallest date in the (now sorted!) array: lower(m.valid_during) = m.valid_on[1]. I would enforce that with a CHECK constraint.

Here we need to create one or two new rows In the 2nd case it is enough to shrink the range of the old row and insert one new row In the 3rd case we update the old row with the left half of array and range, insert the new row and finally insert the with the right half of array and range.

Helper functions

To keep it simple I introduce a new constraint: every array is sorted. Use this helper function

-- sort array
CREATE OR REPLACE FUNCTION f_array_sort(anyarray) 
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
$$SELECT ARRAY (SELECT unnest($1) ORDER BY 1)$$;

I don't need your helper function arraymin() any more, but it could be simplified to:

CREATE OR REPLACE FUNCTION f_array_min(anyarray) 
  RETURNS anyelement LANGUAGE sql IMMUTABLE AS
$$SELECT min(a) FROM unnest($1) a$$;

Two more to get the left and right half of an array split at a given element:

-- split left array at given element
CREATE OR REPLACE FUNCTION f_array_left(anyarray, anyelement) 
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
$$SELECT ARRAY (SELECT * FROM unnest($1) a WHERE a < $2 ORDER BY 1)$$;

-- split right array at given element
CREATE OR REPLACE FUNCTION f_array_right(anyarray, anyelement) 
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
$$SELECT ARRAY (SELECT * FROM unnest($1) a WHERE a >= $2 ORDER BY 1)$$;

Query

This does all the rest:

WITH u AS (  -- identify candidates
   SELECT m.id, rec_id, m.val, m.valid_on, m.valid_during
        , u.val AS u_val, u.valid_on AS u_valid_on
   FROM   master  m
   JOIN   updates u USING (rec_id)
   WHERE  m.val <> u.val
   AND    m.valid_during @> u.valid_on
   FOR    UPDATE  -- lock for update
   )
, upd1 AS (  -- case 2: no overlap, no split
   UPDATE master m  -- shrink old row
   SET    valid_during = daterange(lower(u.valid_during), u.u_valid_on)
   FROM   u
   WHERE  u.id = m.id
   AND    u.u_valid_on > m.valid_on[array_upper(m.valid_on, 1)]
   RETURNING m.id
   )
, ins1 AS (  -- insert new row
   INSERT INTO master (rec_id, val, valid_on, valid_during)
   SELECT u.rec_id, u.u_val, ARRAY[u.u_valid_on]
        , daterange(u.u_valid_on, upper(u.valid_during))
   FROM   upd1
   JOIN   u USING (id)
   )
, upd2 AS (  -- case 3: overlap, need to split row
   UPDATE master m  -- shrink to first half
   SET    valid_during = daterange(lower(u.valid_during), u.u_valid_on)
        , valid_on = f_array_left(u.valid_on, u.u_valid_on)
   FROM   u
   LEFT   JOIN upd1 USING (id)
   WHERE  upd1.id IS NULL  -- all others
   AND    u.id = m.id
   RETURNING m.id, f_array_right(u.valid_on, u.u_valid_on) AS arr_right
   )
INSERT INTO master (rec_id, val, valid_on, valid_during)
          -- new row
SELECT u.rec_id, u.u_val, ARRAY[u.u_valid_on]
     , daterange(u.u_valid_on, upd2.arr_right[1])
FROM   upd2
JOIN   u USING (id)
UNION ALL  -- second half of old row
SELECT u.rec_id, u.val, upd2.arr_right
     , daterange(upd2.arr_right[1], upper(u.valid_during))
FROM   upd2
JOIN   u USING (id);

SQL Fiddle.

Notes

You need to understand the concept of data-modifying CTEs (writeable CTEs), before you touch this. Judging from the code you provided, you know your way around Postgres.
- Are SELECT type queries the only type that can be nested?
FOR UPDATE is to avoid race conditions with concurrent write access. If you are the only user writing to the tables, you don't need it.
I took a piece of paper and drew a timeline so not to get lost in all of this.
Each row is only updated / inserted once, and operations are simple and roughly optimized. No expensive window functions. This should perform well. Much faster than your previous approach in any case.
It would be a bit less confusing if you'd use distinct column names for u.valid_on and m.valid_on, which are related but different things.
I compute the right half of the split array in the RETURNING clause of CTE upd2: f_array_right(u.valid_on, u.u_valid_on) AS arr_right, because I need it several times in the next step. This is a (legal) trick to save one more CTE.
As for solutions that don't involve unnesting the master table: You have to unnest the array valid_on either way, to split it, at least as long as it's not sorted. Also, your helper function arraymin() is already unnesting it anyway.