database - 使 Amazon-Redshift 查询计算平均值适应查询创建元素列表

Question

我对 Amazon Redshift 的语法不是很熟悉，所以我的同事帮助我编写了一个查询来计算一列数字的平均值。我对语法不是很熟悉，我想对其进行调整以创建一个包含所有数字的列表。

我要创建的列表是嵌入在连接中的元素列表，这使得操作更加复杂。我的同事帮我写的查询如下：

  ''' --- Query written with co-worker's help
     SELECT  LOWER(some_query_query) as query,
             AVG(n_results::FLOAT)::FLOAT as 
             n_results_avg,
             count(*) as data_count
     from some_field
     JOIN
       (SELECT
          request_id,
          some_id,
          count(*) as n_results
       from s_results
       WHERE type_name = 'tinder_match'
       AND time <= '2019-06-20'
       AND time >= '2019-06-19'
       GROUP BY request_id, some_id) as n_count
     ON n_count.request_id = some_field.request_id
     WHERE time <= '2019-06-20'
     AND time >= '2019-06-19'
     AND language = 'en'
     AND country = 'US'
     GROUP BY  LOWER(some_query_query)
     ORDER BY n_results_avg DESC 
     --- Current Behaviour: Returns a table with query, 
         n_results_avg, data_count as columns 
     --- Desired Behaviour: Returns a table with query, 
         list_of_name_match_results, data_count as columns
     --- list_of_name_match_results is a list containing all name 
         match results (numbers)
      '''

实际结果：以查询、name_match_results_avg、data_count 为列的输出表

期望的结果：带有查询的输出表，list_of_name_match_results，data_count 作为列

标签： databaselistamazon-redshiftaverage