SQL - Create an array based on the values of two other columns

user_11044119 0
Viewed 386

I have the following data:

-----------------------------------------
| client_id | link_hash_a | link_hash_b |
-----------------------------------------
|     1     |     abc     |     xyz     |
|     2     |     def     |     xyz     |
|     3     |     def     |     uvw     |
-----------------------------------------

I would like to create an array of client_id that are linked with the two hash values from the columns link_hash_a and link_hash_b using SQL.

In the current situation, the result would be a unique array with the value {1,2,3} because the clients 1 and 2 are linked with the value xyz of the link_hash_b column and the client 2 and 3 are linked with the value def of the link_hash_a column.

Is there a way to do that with an SQL query? Thank you really much for your input.

clickhouse arrays sql
2 Answers

I think I found a way through. I used another column which is the club_id of which the clients are part of. In this case, the clients 1, 2 and 3 are all part of the club_id 1 for example.

Here is my code using ClickHouse SQL and taken into account that input_table is the table of data as shown in the question:

SELECT club_id, arrayConcat( clt_a, clt_b ) as tot_clt_arr, arrayUniq( arrayConcat( clt_a, clt_b ) ) as tot_clt
FROM
(
    SELECT club_id, clt_a
    FROM
        (
        SELECT club_id, link_hash_a, groupUniqArray(client_id) as clt_a
        FROM input_table
        GROUP BY club_id, link_hash_a
        )
    WHERE length(clt_a) >= 2
) JOIN
(
    SELECT club_id, clt_b
    FROM
        (
        SELECT club_id, link_hash_b, groupUniqArray(client_id) as clt_b
        FROM input_table
        GROUP BY club_id, link_hash_b
        )
    WHERE length(clt_b) >= 2
)
USING club_id
GROUP BY club_id, tot_clt_arr;

It returns the array of client_id as well as the number of unique client_id in the tot_clt column.

Thank you @TomášZáluský for your help.

user_110441190

As alternative can be used this way:

SELECT groupUniqArrayArray(client_ids) client_ids
FROM (
  SELECT link_hash, groupArray(client_id) client_ids
  FROM (
    SELECT DISTINCT client_id, arrayJoin([link_hash_a, link_hash_b]) as link_hash
    FROM (
      /* test data */
      SELECT data.1 client_id, data.2 link_hash_a, data.3 link_hash_b
      FROM (
        SELECT arrayJoin([
          (1, 'abc', 'xyz'),
          (2, 'def', 'xyz'),
          (3, 'def', 'uvw')]) data)))
  GROUP BY link_hash
  HAVING count() = 2)
/* result
┌─client_ids─┐
│ [2,1,3]    │
└────────────┘
*/
user_3032980
Related