CODEWITHSUNDEEP
pythonsqlpostgresql
New User
New User

Reputation: 149

Critical - Converting loops of queries to 1 interesting and consolidated query

I am trying to optimize my simple queries into more complex queries.

I have three tables 

Table 1
a_id  info
1     talk
2     talk
3     sleep
4     sit

Table 2 
key data_1 data_2 a_id g_id dat_3
1     6.4  3.2    1    1    a
2     5.6  3.5    1    2    a
3     4.6  6.7    1    3    b
4     1.2  2.3    2    4    c
5     0.9  5.6    3    5    c
6     1.1  1.5    4    6    b

Table 3
g_id dat_1 dat_2
1     x     t
2     x     b
3     y     o
4     y     t

Psuedo code 

#get a_ids from table 1 
for each a_id:
   extract unique g_id from table 2
   for each g_id:
       count number of a,b,and c using union from

I have implemented (in postgresql) the above psuedo code using loops and simple queries. As you can see, I have to run too many queries. This slows down the process, tremendously. How can I do this using just 1 big query - which is lot faster?

Example - Loop Run for Talk, sleep, and sit.

For talk it would be -
a_ids would be 1,2.

For 1 it will find 1,2,3 as g_ids.

For each g_id it will count number of a,b, and c. Count at the the end.

Required output will be :

info  a_id g_id  a  b  c 

talk  2     3    2  1  1
sleep 1     1    0  0  1  
sit   1     1    0  1  0

I am new to the Postgresql database and moving into complex queries. Please help me here. And if you do not like the question then please let me know and I will remove it. Do not downvote. Thanks!

Upvotes: 1

Views: 58

Answers (1)

D-Shih
D-Shih

Reputation: 46219

you can try to JOIN with condition aggregate function

CREATE TABLE Table1(
   a_id INT,
   info VARCHAR(5)
);

INSERT INTO Table1 values (1,'talk');
INSERT INTO Table1 values (2,'talk');
INSERT INTO Table1 values (3,'sleep');
INSERT INTO Table1 values (4,'sit');


CREATE TABLE Table2(
  a_id INT,
  g_id INT,
  dat_3 VARCHAR(5)
);




INSERT INTO Table2 values (1,1,'a');
INSERT INTO Table2 values (1,2,'a');
INSERT INTO Table2 values (1,3,'b');
INSERT INTO Table2 values (2,4,'c');
INSERT INTO Table2 values (3,5,'c');
INSERT INTO Table2 values (4,6,'b');

Query 1:

SELECT t1.info,
       COUNT(DISTINCT t1.a_id) a_id,
       COUNT(DISTINCT t2.dat_3) g_id,
       sum((dat_3 = 'a')::int) a,
       sum((dat_3 = 'b')::int) b,
       sum((dat_3 = 'c')::int) c
FROM Table1 t1 
INNER JOIN Table2 t2 on t1.a_id = t2.a_id
group by t1.info
order by t1.info desc

Results:

|  info | a_id | g_id | a | b | c |
|-------|------|------|---|---|---|
|  talk |    2 |    3 | 2 | 1 | 1 |
| sleep |    1 |    1 | 0 | 0 | 1 |
|   sit |    1 |    1 | 0 | 1 | 0 |

Note

  1. sum((dat_3 = 'a')::int) can use bool to int then sum, if condition is true then 1 otherwise 0
  2. sum condition depend on dat_3 column. you can dynamic add the condition in your logice.

Upvotes: 1

Related Questions