Multi-value column

Create a table

In this example, a table named test1 is created, and the tags field is defined as a multi-value column through MULTIVALUE. Fields in the columns are separated by commas (,). The multi-value column contains the value_type field of the VARCHAR type.

CREATE TABLE test1 (
    user_id BIGINT,
    city VARCHAR,
    tags MULTIVALUE delimiter_tokenizer ',' value_type 'varchar'
)
DISTRIBUTED BY HASH (user_id)

Write data into a table

Execute the following statements to write three data records into the test1 table:

insert into test1 values(1, 'HZ', 'A,B,C');
insert into test1 values(2, 'BJ', 'B,D');
insert into test1 values(3, 'SH', 'A,C,D,F');

select * from test1 order by user_id;
+---------+------+---------+
| user_id | city | tags    |
+---------+------+---------+
|       1 | HZ   | A,B,C   |
|       2 | BJ   | B,D     |
|       3 | SH   | A,C,D,F |

Query data

• Query by filtering

  Execute the following statement to query the total number of users that contain either the A or B tag in the test1 table:

  select count(*) from test1 where ref(tags,0) in ('A', 'B');
  +----------+
  | COUNT(*) |
  +----------+
  |        3 |

  Execute the following statement to query the number of users that contain both the A and B tags in the test1 table:

  select * from test1 where ref(tags,0) in ('A') and ref(tags,0) in ('B');
  +---------+------+-------+
  | user_id | city | tags  |
  +---------+------+-------+
  |       1 | HZ   | A,B,C |

• Query by grouping

  Execute the following statement to query the number of users that do not locate in the Hangzhou region and contain the A tag and the number of users that do not locate in the Hangzhou region and contain the B tag in the test1 table:

  select ref(tags,0), count(*) from test1
  where ref(tags,0) in ('A', 'B') and city ! = 'HZ' group by ref(tags,0);
  +--------------+----------+
  | ref(tags, 0) | COUNT(*) |
  +--------------+----------+
  | A            |        1 |
  | B            |        1 |

  Execute the following statement to query the number of users that contain the A or B tag in each corresponding city in the test1 table:

  select ref(tags,0), city, count(*) from test1
  where ref(tags,0) in ('A', 'B') group by ref(tags,0), city;
  +--------------+----------+
  | ref(tags, 0) | COUNT(*) |
  +--------------+----------+
  | B            | BJ       |
  | B            | HZ       |
  | A            | SH       |
  | A            | HZ       |

• Query by joining

  select count(*) from test1 join test
  on test1.user_id = test.id where ref(tags,0) in ('A', 'B') ;
  +----------+
  | COUNT(*) |
  +----------+
  |        0 |

Precautions

• Multi-value column conditions can only be used for queries in a single table. Multi-value column conditions cannot be used for queries in joined tables.
• If the group specified by the Group By field contains a multi-value column, the WHERE condition must also contain the multi-value column. Otherwise, the engine layer of AnalyticDB for MySQL will exhaustively list all values of the multi-value column, which will affect query performance.
  Note By default, 1,024 values of a multi-value column are listed.