Question 160592 in PostgreSQL Question posted in
The official documentation can be found here.

Can Postgresql join one table to another based on conditions in the second table?

I have two tables. I want to create a SQL query such that for each row in Table 1, I want find the first two rows in Table 2 such ms.Table2 > ms.Table1. The resulting output will be Table 1 with six additional columns added showing ms, bid, ask from Table 2 corresponding to first two rows from Table 2 where ms is just greater than ms in Table 1.

Given the large number of rows, doing a cross-join and then filtering may be too much (since this has to be done many times over multiple data, not just once). Looking for an efficient solution. I am new to SQL so feel this is bit much for me, though I did try a cross-join. Please help!

Table 1 (2000+ rows)

ms price bid ask
34200106 1.48 1.46 1.5
34200106 1.48 1.46 1.5
34202812 1.56 1.53 1.56
34202815 1.56 1.53 1.56
34202823 1.56 1.53 1.56
34204227 1.56 1.53 1.57
34207137 1.56 1.55 1.57
34208496 1.57 1.56 1.57
34208938 1.57 1.55 1.57
34208938 1.57 1.55 1.57
34208943 1.57 1.55 1.57
34208951 1.57 1.56 1.57

Table 2 (300000+ rows)

ms bid ask
34200009 1.46 1.51
34200011 1.46 1.5
34200106 1.46 1.5
34200171 1.46 1.5
34200195 1.46 1.5
34200195 1.46 1.51
34200201 1.46 1.52
34200202 1.46 1.52
34200276 1.46 1.52
34200296 1.46 1.52
34200305 1.46 1.52
34200306 1.46 1.52
34200308 1.46 1.53
34200326 1.46 1.53
Tags:

2

Answers


  1. 0

    you could use a loop, but it will still be somewhat slow, but much faster than a cross join

    CREATE TABLE table1
    ("ms" int, "price" int, "bid" int, "ask" int)
;
    
INSERT INTO table1
    ("ms", "price", "bid", "ask")
VALUES
    (34200106, 1.48, 1.46, 1.5),
    (34200106, 1.48, 1.46, 1.5),
    (34202812, 1.56, 1.53, 1.56),
    (34202815, 1.56, 1.53, 1.56),
    (34202823, 1.56, 1.53, 1.56),
    (34204227, 1.56, 1.53, 1.57),
    (34207137, 1.56, 1.55, 1.57),
    (34208496, 1.57, 1.56, 1.57),
    (34208938, 1.57, 1.55, 1.57),
    (34208938, 1.57, 1.55, 1.57),
    (34208943, 1.57, 1.55, 1.57),
    (34208951, 1.57, 1.56, 1.57)
;

    CREATE TABLE

    INSERT 0 12

    CREATE TABLE table2
    ("ms" int, "bid" int, "ask" int)
;
    
INSERT INTO table2
    ("ms", "bid", "ask")
VALUES
    (34200009, 1.46, 1.51),
    (34200011, 1.46, 1.5),
    (34200106, 1.46, 1.5),
    (34200171, 1.46, 1.5),
    (34200195, 1.46, 1.5),
    (34200195, 1.46, 1.51),
    (34200201, 1.46, 1.52),
    (34200202, 1.46, 1.52),
    (34200276, 1.46, 1.52),
    (34200296, 1.46, 1.52),
    (34200305, 1.46, 1.52),
    (34200306, 1.46, 1.52),
    (34200308, 1.46, 1.53),
    (34200326, 1.46, 1.53)
;

    CREATE TABLE

    INSERT 0 14

    CREATE OR REPLACE FUNCTION foo() 
RETURNS TABLE ("ms" int, "price" int, "bid" int, "ask" int)
  language plpgsql as $$
DECLARE 
  x int ;
  table1_cursor CURSOR FOR SELECT t1."ms" FROM table1 t1; 
BEGIN
  CREATE TEMP TABLE IF NOT EXISTS temp_table AS
    SELECT *
    FROM table1;

  FOR x IN table1_cursor loop
    INSERT INTO temp_table ("ms", "price", "bid", "ask")
     SELECT t2."ms", NULL, t2."bid", t2."ask" FROM table2 t2
  WHERE t2."ms" > x."ms"
      ORDER BY t2."ms" 
       LIMIT 2;
  END loop;

RETURN QUERY 
SELECT t3."ms", t3."price", t3."bid", t3."ask"
from temp_table t3;
    DROP TABLE temp_table;

END $$;

SELECT * FROM foo();

    CREATE FUNCTION
    ms price bid ask
    34200106 1 1 2
    34200106 1 1 2
    34202812 2 2 2
    34202815 2 2 2
    34202823 2 2 2
    34204227 2 2 2
    34207137 2 2 2
    34208496 2 2 2
    34208938 2 2 2
    34208938 2 2 2
    34208943 2 2 2
    34208951 2 2 2
    34200171 null 1 2
    34200195 null 1 2
    34200171 null 1 2
    34200195 null 1 2 
    SELECT 16

    fiddle

    Login or Signup to reply.
  2. 0

    The following query produces the described results without doing a full cross join between table1 and table2:

    SELECT table1.ms,
       table1.bid,
       table1.ask,
       t2.ms[1] AS ms1,
       t2.bids[1] AS bid1,
       t2.asks[1] AS ask1,
       t2.ms[2] AS ms2,
       t2.bids[2] AS bid2,
       t2.asks[2] AS ask2
  FROM table1
  CROSS JOIN LATERAL (SELECT ARRAY_AGG(t.ms) AS ms,
                             ARRAY_AGG(t.bid) AS bids,
                             ARRAY_AGG(t.ask) AS asks
                        FROM (SELECT table2.*
                                FROM table2
                               WHERE table2.ms > table1.ms
                               ORDER BY table2.ms
                               LIMIT 2
                             ) t
                     ) AS t2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search