如何在Google BigQuery中执行trigram操作？ [英] How to perform trigram operations in Google BigQuery?

问题描述

我使用PostgreSQL中的 pg_trgm 模块来计算两个字符串之间使用卦的相似度。特别是我使用：

$ p $ similar（text，text）


哪个返回值返回一个数字，表示两个参数（0和1之间）的相似程度。

如何在Google BigQuery上执行相似性功能（或相当功能）？

>

请在下面尝试。至少作为增强的蓝图

  SELECT text1，text2，similarity FROM 
 JS（
 //输入表
（
 SELECT * FROM 
（SELECT'mikhail'AS text1，'mikhail'AS text2），
（SELECT'mikhail'AS text1，'mike'AS text2 ），
（SELECT'mikhail'AS text1，'michael'AS text2），
（SELECT'mikhail'AS text1，'javier'AS text2），
（SELECT'mikhail'AS text1，'thomas'AS text2）
），
 //输入列
 text1，text2，
 //输出模式
[{name：'text1' ，输入：'string'}，
 {name：'text2'，类型：'string'}，
 {name：'similarity'，type：'float'}] 
， 
 // function 
function（r，emit）{
 
 var _extend = function（dst）{
 var sources = Array.prototype.slice.call参数，1）; 
 for（var i = 0; i< sources.length; ++ i）{
 var src = sources [i]; 
 for（var p in src） {
 if（src.hasOwnProper ty（p））dst [p] = src [p]; 
} 
} 
 return dst; 
}; 
 
 var Levenshtein = {
 / ** 
 *计算两个琴弦的levenshtein距离。 
 * 
 * @param str1字符串，第一个字符串。 
 * @param str2第二个字符串的字符串。 
 * @return整数levenshtein距离（0和以上）。 
 * / 
 get：function（str1，str2）{
 // base cases 
 if（str1 === str2）return 0; 
 if（str1.length === 0）return str2.length; 
 if（str2.length === 0）return str1.length; 
 
 //两行
 var prevRow = new Array（str2.length + 1），
 curCol，nextCol，i，j，tmp; 
 
 //初始化上一行
 for（i = 0; i< prevRow.length; ++ i）{
 prevRow [i] = i; 
} 
 
 //计算当前行距前一行
（i = 0; i  nextCol = i + 1; $ j 
 $ b for（j = 0; j  curCol = nextCol; 
 
 // substution 
 nextCol = prevRow [j] +（（str1.charAt（i）=== str2.charAt（j））？0：1）; 
 //插入
 tmp = curCol + 1; 
 if（nextCol> tmp）{
 nextCol = tmp; 
} 
 //删除
 tmp = prevRow [j + 1] + 1; 
 if（nextCol> tmp）{
 nextCol = tmp; 
} 
 
 //将当前col值复制到previous（准备进行下一次迭代）
 prevRow [j] = curCol; 
} 
 
 //将last col值复制到previous（准备下一次迭代）
 prevRow [j] = nextCol; 
} 
 
 return nextCol; 
} 
 
}; 
 
 var the_text1; 
 
尝试{
 the_text1 = decodeURI（r.text1）.toLowerCase（）; 
} catch（ex）{
 the_text1 = r.text1.toLowerCase（）; 
} 
 
尝试{
 the_text2 = decodeURI（r.text2）.toLowerCase（）; 
} catch（ex）{
 the_text2 = r.text2.toLowerCase（）; $ {
} 
 
 emit（{text1：the_text1，text2：the_text2，
 similarity：1  -  Levenshtein.get（the_text1，the_text2）/ the_text1.length}）; 
 
}
）
 ORDER BY相似度DESC 
  

这是根据 https://storage.googleapis.com进行轻微修改/thomaspark-sandbox/udf-examples/pataky.js by @thomaspark

I do use the pg_trgm module in PostgreSQL to calculate similarity between two strings using trigrams. Particularly I use:

similarity(text, text)

Which returns returns a number that indicates how similar the two arguments are (between 0 and 1).

How can I perform similarity function (or equivalent) on Google BigQuery?

解决方案

Try below. At least as a blueprint for enhancing

SELECT text1, text2, similarity FROM 
JS(
// input table
(
  SELECT * FROM 
  (SELECT 'mikhail' AS text1, 'mikhail' AS text2),
  (SELECT 'mikhail' AS text1, 'mike' AS text2),
  (SELECT 'mikhail' AS text1, 'michael' AS text2),
  (SELECT 'mikhail' AS text1, 'javier' AS text2),
  (SELECT 'mikhail' AS text1, 'thomas' AS text2)
) ,
// input columns
text1, text2,
// output schema
"[{name: 'text1', type:'string'},
  {name: 'text2', type:'string'},
  {name: 'similarity', type:'float'}]
",
// function
"function(r, emit) {

  var _extend = function(dst) {
    var sources = Array.prototype.slice.call(arguments, 1);
    for (var i=0; i<sources.length; ++i) {
      var src = sources[i];
      for (var p in src) {
        if (src.hasOwnProperty(p)) dst[p] = src[p];
      }
    }
    return dst;
  };

  var Levenshtein = {
    /**
     * Calculate levenshtein distance of the two strings.
     *
     * @param str1 String the first string.
     * @param str2 String the second string.
     * @return Integer the levenshtein distance (0 and above).
     */
    get: function(str1, str2) {
      // base cases
      if (str1 === str2) return 0;
      if (str1.length === 0) return str2.length;
      if (str2.length === 0) return str1.length;

      // two rows
      var prevRow  = new Array(str2.length + 1),
          curCol, nextCol, i, j, tmp;

      // initialise previous row
      for (i=0; i<prevRow.length; ++i) {
        prevRow[i] = i;
      }

      // calculate current row distance from previous row
      for (i=0; i<str1.length; ++i) {
        nextCol = i + 1;

        for (j=0; j<str2.length; ++j) {
          curCol = nextCol;

          // substution
          nextCol = prevRow[j] + ( (str1.charAt(i) === str2.charAt(j)) ? 0 : 1 );
          // insertion
          tmp = curCol + 1;
          if (nextCol > tmp) {
            nextCol = tmp;
          }
          // deletion
          tmp = prevRow[j + 1] + 1;
          if (nextCol > tmp) {
            nextCol = tmp;
          }

          // copy current col value into previous (in preparation for next iteration)
          prevRow[j] = curCol;
        }

        // copy last col value into previous (in preparation for next iteration)
        prevRow[j] = nextCol;
      }

      return nextCol;
    }

  };

  var the_text1;

  try {
    the_text1 = decodeURI(r.text1).toLowerCase();
  } catch (ex) {
    the_text1 = r.text1.toLowerCase();
  }

  try {
    the_text2 = decodeURI(r.text2).toLowerCase();
  } catch (ex) {
    the_text2 = r.text2.toLowerCase();
  }

  emit({text1: the_text1, text2: the_text2,
        similarity: 1 - Levenshtein.get(the_text1, the_text2) / the_text1.length});

  }"
)
ORDER BY similarity DESC

This is light modification based on https://storage.googleapis.com/thomaspark-sandbox/udf-examples/pataky.js by @thomaspark

这篇关于如何在Google BigQuery中执行trigram操作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！