如何通过levenshtein距离按相似列合并行

Question

我正在使用AWS Athena，我正在尝试合并具有特定列且levenshtein_distance值小于5的所有行，并将归一化百分比相加。

该表的结构如下：

sql prettyprint-override">CREATE EXTERNAL TABLE `actions`(
  `id` string COMMENT 'from deserializer', 
  `text` string COMMENT 'from deserializer',
  `normalizedpercentage` float COMMENT 'from deserializer', 
  `timestamp` timestamp COMMENT 'from deserializer')
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION
  's3://xxxxxx/db/actions'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'transient_lastDdlTime'='1566991410')

WITH t AS 
    (SELECT id,
         text,
         normalizedPercentage
    FROM actions
    WHERE actions.timestamp
        BETWEEN timestamp '2019-08-01 00:00:01'
            AND timestamp '2019-08-31 23:59:59' )
SELECT *,
         SUM(normalizedPercentage)
    OVER (PARTITION BY levenshtein_distance(text, EVERY_OTHER_TEXT_COLUMN) < 5) AS cumulative
FROM t

Answer 1

您可以根据您的函数计算临时表中的新列，然后使用该列在主查询中进行分区

WITH t AS 
(SELECT id,
     text,
     normalizedPercentage,case when  levenshtein_distance(text, EVERY_OTHER_TEXT_COLUMN) < 5 then 'groupA' else 'groupB' end as classification
FROM actions
WHERE actions.timestamp
    BETWEEN timestamp '2019-08-01 00:00:01'
        AND timestamp '2019-08-31 23:59:59' )
   SELECT *,
     SUM(normalizedPercentage)
OVER (PARTITION BY classification ) AS cumulative
FROM t