SQL(Omnisci)获取列的常见和不常见值

huangapple go评论75阅读模式
英文:

SQL (Omnisci) get common and uncommon values of a column

问题

我正在使用Omnisci连接两个表格,我需要以下内容:

表格1:

poly_id | num_competitors
   1    |     1
   2    |     1
   3    |     5

表格2:

    poly_id | num_stores
       1    |     1
       5    |     3
       7    |     5

我想要的是:

 poly_id | num_competitors | num_stores
       1    |     1        |    1
       2    |     1        |    0
       3    |     5        |    0
       5    |     0        |    3
       7    |     0        |    5

我知道在普通的SQL中,你可以使用FULL JOIN或者UNION来实现,但是Omnisci目前还不支持这些函数(但支持JOIN和LEFT JOIN)。

英文:

I'm using Omnisci to join two tables and I need the following:

Table 1:

poly_id | num_competitors
   1    |     1
   2    |     1
   3    |     5

Table 2:

    poly_id | num_stores
       1    |     1
       5    |     3
       7    |     5

What I want:

 poly_id | num_competitors | num_stores
       1    |     1        |    1
       2    |     1        |    0
       3    |     5        |    0
       5    |     0        |    3
       7    |     0        |    5

I know in normal SQL you can do it with FULL JOIN or even with UNION, but Omnisci does not support any of these functions yet (it does support JOIN and LEFT JOIN though).

答案1

得分: 1

我找到了一种解决方法。通过创建一个新的空表来解决。将Table 1和Table 2插入其中,然后按poly id进行分组,以合并具有num_competitors和num_stores的行。

CREATE TABLE competitors_stores (poly_id integer, num_stores integer, num_competitors integer);

INSERT INTO competitors_stores (SELECT poly_id, 0, num_competitors FROM competitors_geo);

INSERT INTO competitors_stores (SELECT poly_id, num_stores, 0 FROM telepi_stores_geo);

CREATE TABLE num_competitors_stores AS (SELECT poly_id, SUM(num_stores) AS num_stores, SUM(num_competitors) AS num_competitors FROM competitors_stores GROUP BY poly_id);

DROP TABLE telepi_competitors_stores;

无论如何,我仍然愿意听取其他解决方案,因为我觉得这不是最佳解决方法。

英文:

I've found a way to solve it. It's by creating a new empty table. Insert into it Table 1 and Table 2 and then make a group by on poly id in order to merge rows that have both num_competitors and num_stores.

CREATE TABLE competitors_stores ( poly_id integer, num_stores integer, num_competitors integer);

INSERT INTO competitors_stores ( SELECT poly_id, 0, num_competitors from competitors_geo)

INSERT INTO competitors_stores ( SELECT poly_id, num_stores, 0 from telepi_stores_geo)

CREATE TABLE num_competitors_stores AS (select poly_id, SUM(num_stores) AS num_stores, SUM(num_competitors) as num_competitors from competitors_stores group by poly_id);

DROP TABLE telepi_competitors_stores;

Anyway, I'm still open to hearing alternatives since I feel like this is not the best way to solve it.

huangapple
  • 本文由 发表于 2020年1月3日 18:13:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/59576698.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定