Cross join a users table to add a "next_updated_at" for each id
I essentially want to find the "next_updated_at" for each user_id
The Users tables important fields are user_id, id, updated_at
My thought was to try to cross join the users table to find the minimum updated_at for each user_id where the joined tables id is greater than the main tables id
SELECT * FROM users m1 LEFT JOIN ( SELECT min(updated_at),user_id FROM users GROUP BY user_id ) m2 ON m2.user_id = m1.user_id AND m2.id > m1.id
But id doesn’t exist in the subquery… I’m kind of stumped
user_id id updated_at 12345 1 01-01-2018 12345 2 03-05-2018 11223 3 03-07-2018 33211 4 04-11-2018 11223 5 06-11-2018 11223 6 11-01-2018 33211 7 01-04-2019 33211 8 01-07-2019
user_id id updated_at next_updated_at 12345 1 01-01-2018 03-05-2018 12345 2 03-05-2018 11223 3 03-07-2018 06-11-2018 33211 4 04-11-2018 01-04-2019 11223 5 06-11-2018 11-01-2018 11223 6 11-01-2018 33211 7 01-04-2019 01-07-2019 33211 8 01-07-2019
SELECT u.*, LEAD(updated_at) OVER (PARTITION BY user_id ORDER BY updated_at, id) as next_updated_at FROM users u;
I'm not sure why you would want to use the
id. It seems that the next updated would be based on the date itself. I added the
id to handle ties.
SQL Server Cross Join Illustrated By Practical Examples, is to create obtain all combinations of items, such as colors and sizes. If you add a WHERE clause, in case table t1 and t2 has a relationship, the CROSS JOIN works like the INNER JOIN clause as shown in the following query: SELECT * FROM t1 CROSS JOIN t2 WHERE t1.id = t2.id; MySQL CROSS JOIN clause examples. Let’s set up some tables to demonstrate the CROSS JOIN clause. Setting up sample tables. First, create a new database salesdb: CREATE DATABASE IF NOT EXISTS salesdb; Second, switch the current data to the new database salesdb: USE salesdb;
This looks to be perhaps the easiest and quite possibly the most performant query and avoids using
SELECT o.user_id ,o.id ,o.updated_at ,(SELECT TOP 1 i.updated_at FROM users i WHERE i.user_id = o.user_id AND i.updated_at > o.updated_at ORDER BY i.updated_at ASC) AS next_updated_at FROM users o
What is the difference between Cross Join and Inner Join in MySQL , does not establish a relationship between the joined tables. The query above returns the addresses and users tables, cross joined. The result set consists of every record in users mapped to every record in addresses. For 4 users and 3 addresses, we get a total of 4x3=12 records. In mathematical terms, this is the cross product of a set. In an application, it's very unlikely that you would use a CROSS JOIN. Most of the time, it's more important to match rows together through a join condition in order to return a meaningful result.
I believe the redshift flavour of PG supports simple analytic functions like lead:
SELECT user_id, id, updated_at, LEAD(updated_at) OVER(PARTITION BY user_id ORDER BY id) as next_updated_at FROM users
Lead selects the updated_at from the next row (order defined by ID, could have used updated_at as well/instead) within the same set of user_id (the partition means every distinct user id works as a set)
I conceive mentally that LEAD breaks the result set up into groups of user ids ordered by date, and then for any given row, the lead row is the next one after it. It's actually a bit more like analytic functions establish a separate, possibly grouped, coordinated result set that is automatically associated with the current row in some way.. but whatever helps your mental model :)
Going your way something like this:
SELECT a.user_id, a.id, a.updated_at, min(z.updated_at) FROM users a LEFT OUTER JOIN users z ON a.user_id = z.userid and a.id < z.id GROUP BY a.user_id, a.id, a.updated_at
This joins all rows from a user to all other rows of the same user where the id is greater, then uses grouping to find only the min greater id per other id from that same user
user_id id updated_at 11223 3 03-07-2018 11223 5 06-11-2018 11223 6 11-01-2018
After self join on userid and id
a.userid a.id a.updated_at z.userid z.id z.updatedat 11223 3 03-07-2018 11223 5 06-11-2018 11223 3 03-07-2018 11223 6 11-01-2018 11223 5 06-11-2018 11223 6 11-01-2018 11223 6 11-01-2018
Then the group by min zaps the later rows (row 2)
SQL - CARTESIAN or CROSS JOINS, What is the difference between cross join and inner join? Takes something like a digits table, which has ten rows for the digits 0-9. You can use cross join on that table a few times to a get result that has however many rows you need, with the results numbered appropriately. This has a number of uses. For example, you can combine it with a datadd() function to get a set for every day in a given year.
SQL Cross Join, The CROSS JOIN joined every row from the first table (T1) with every row from the second table (T2). In other words, the cross join returns a Cartesian product of rows from both tables. Unlike the INNER JOIN or LEFT JOIN , the cross join does not establish a relationship between the joined tables. The expression to generate the cross join is presented below: CROSSJOIN( Colors, Stationery) When the above expression is used wherever a table expression is expected, the results of the expression would be as follows:
A Practical Use of MySQL CROSS JOIN Clause, The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table. To perform a join you need two items: two tables and a join condition. The tables contain the rows to combine, and the join condition the instructions to match rows together. Take a look at the following Venn diagram. The circles represent the tables and where they overlap rows satisfying the join condition.
Cross join a users table to add a "next_updated_at" for each id , This tutorial shows you how to use the MySQL CROSS JOIN clause to create the Cartesian product of rows from the joined tables. This type of JOIN returns the cartesian product of rows from the tables in Join. It will return a table which consists of records which combines each row from the first table with each row of the second table. Cross JOIN Syntax is, SELECT column-name-list from table-name1 CROSS JOIN table-name2; Self JOIN. A self JOIN is a regular join, but the table is joined with itself.