Maxcompute中计算差集、交集和并集

阳光穿透心脏的1/2处 2022-04-24 01:30 469阅读 0赞

源表：portrait1和portrait2

代码： 

  1. --1 差集
  2. CREATE TABLE IF NOT EXISTS  analysis_except
  3. (
  4.     uid BIGINT
  5. ) lifecycle ${lifecycle};
  7. set odps.stage.mapper.split.size= 1;
  8. INSERT OVERWRITE TABLE analysis_except
  9. SELECT u.uid from analysis_portrait1 u 
  10. left join analysis_portrait2 v
  11. on u.uid=v.uid
  12. where v.uid is null;
  14. --2 交集
  15. CREATE TABLE IF NOT EXISTS  analysis_intersect
  16. (
  17.     uid BIGINT
  18. ) lifecycle ${lifecycle};
  20. set odps.stage.mapper.split.size= 1;
  21. INSERT OVERWRITE TABLE analysis_intersect
  22. SELECT u.uid FROM analysis_portrait1 u
  23. INTERSECT
  24. SELECT v.uid FROM analysis_portrait2 v;
  26. --3 并集
  27. CREATE TABLE IF NOT EXISTS  analysis_union
  28. (
  29.     uid BIGINT
  30. ) lifecycle ${lifecycle};
  32. set odps.stage.mapper.split.size= 1;
  33. INSERT OVERWRITE TABLE analysis_union
  34. SELECT u.uid FROM analysis_portrait1 u
  35. UNION
  36. SELECT v.uid FROM analysis_portrait2 v;

另外，非MYSQL的应该可以用 EXCEPT 来求差集。

发表评论

表情：
评论列表 （有 0 条评论，469人围观）

还没有评论，来说两句吧...

相关阅读