多表、多行SQL select

Question

根据下面的模式，我如何列出有关自由职业者的所有信息？包括利基，语言，市场等。我的问题是，每个自由职业者可以有每个表的多个条目。那么，我该怎么做呢？是否可以使用SQL，或者我是否需要使用我的主要语言(golang)来实现这一点？

CREATE TABLE freelancer (
  freelancer_id         SERIAL PRIMARY KEY,
  ip                    inet NOT NULL,
  username              VARCHAR(20) NOT NULL,
  password              VARCHAR(100) NOT NULL,
  email                 citext NOT NULL UNIQUE,
  email_verified        int NOT NULL,
  fname                 VARCHAR(20) NOT NULL,
  lname                 VARCHAR(20) NOT NULL,
  phone_number          VARCHAR(30) NOT NULL,
  address               VARCHAR(50) NOT NULL,
  city                  VARCHAR(30) NOT NULL,
  state                 VARCHAR(30) NOT NULL,
  zip                   int NOT NULL,
  country               VARCHAR(30) NOT NULL,
);

CREATE TABLE market (
market_id       SERIAL PRIMARY KEY,
market_name     VARCHAR(30) NOT NULL,
);

CREATE TABLE niche (
niche_id        SERIAL PRIMARY KEY,
niche_name      VARCHAR(30) NOT NULL,
);

CREATE TABLE medium (
medium_id       SERIAL PRIMARY KEY,
medium_name     VARCHAR(30) NOT NULL,
);

CREATE TABLE format (
format_id       SERIAL PRIMARY KEY,
format_name     VARCHAR(30) NOT NULL,
);

CREATE TABLE lang (
lang_id         SERIAL PRIMARY KEY,
lang_name       VARCHAR(30) NOT NULL,
);

CREATE TABLE freelancer_by_niche (
id      SERIAL PRIMARY KEY,
niche_id        int NOT NULL REFERENCES niche (niche_id),
freelancer_id   int NOT NULL REFERENCES freelancer (freelancer_id)
);


CREATE TABLE freelancer_by_medium (
id      SERIAL PRIMARY KEY,
medium_id       int NOT NULL REFERENCES medium (medium_id),
freelancer_id   int NOT NULL REFERENCES freelancer (freelancer_id)

);

CREATE TABLE freelancer_by_market (
id      SERIAL PRIMARY KEY,
market_id       int NOT NULL REFERENCES market (market_id),
freelancer_id   int NOT NULL REFERENCES freelancer (freelancer_id)
);

CREATE TABLE freelancer_by_format (
id      SERIAL PRIMARY KEY,
format_id       int NOT NULL REFERENCES format (format_id),
freelancer_id   int NOT NULL REFERENCES freelancer (freelancer_id)

);

CREATE TABLE freelancer_by_lang (
id      SERIAL PRIMARY KEY,
lang_id         int NOT NULL REFERENCES lang (lang_id),
freelancer_id   int NOT NULL REFERENCES freelancer (freelancer_id)

);

Answer 1

SELECT *  
FROM freelancer  
INNER JOIN freelancer_by_niche USING (freelancer_id)  
INNER JOIN niche USING (niche_id)  
INNER JOIN freelancer_by_medium USING (freelancer_id)  
INNER JOIN medium USING (medium_id)  
INNER JOIN freelancer_by_market USING (freelancer_id)  
INNER JOIN market USING (market_id)  
INNER JOIN freelancer_by_format USING (freelancer_id)  
INNER JOIN format USING (format_id)  
INNER JOIN freelancer_by_lang USING (freelancer_id)  
INNER JOIN lang USING (lang_id);  

如果您希望丢失连接表(如freelancer_by_format )中不必要的属性，则可以这样做

SELECT a.ip, a.username, a.password, a.email, a.email_verified,  
a.fname, a.lname, a.phone_number, a.address, a.city,  
a.state, a.zip, a.country,  
b.niche_name, c.medium_name, d.market_name, e.format_name, f.lang_name  
FROM freelancer a  
INNER JOIN freelancer_by_niche USING (freelancer_id)  
INNER JOIN niche b USING (niche_id)  
INNER JOIN freelancer_by_medium USING (freelancer_id)  
INNER JOIN medium c USING (medium_id)  
INNER JOIN freelancer_by_market USING (freelancer_id)  
INNER JOIN market d USING (market_id)  
INNER JOIN freelancer_by_format USING (freelancer_id)  
INNER JOIN format e USING (format_id)  
INNER JOIN freelancer_by_lang USING (freelancer_id)  
INNER JOIN lang f USING (lang_id);  

如果您想要更改列名，例如将"market_name“更改为"market"，则可以使用

SELECT a.ip, ... ,  
       d.market_name "market", e.format_name AS "format", ...  
FROM ...  

在您的连接表中注释(例如freelancer_by_niche)在freelancer_id上没有UNIQUE约束，这意味着您可以在多个市场拥有相同的自由职业者(这是可以的，而且可能是有意的)。

但是，你也没有在两个属性(freelancer_id, niche_id)上都有UNIQUE约束，这意味着每个自由职业者可能会多次出现在同一个利基中。(“乔在电子行业工作。三次”)。您可以通过在freelancer_by_niche中创建(freelancer_id, niche_id) UNIQUE来防止这种情况。这样，您也不需要代理(人工) PRIMARY KEY freelancer_by_id (id)。

那么会出什么问题呢？

例如，假设一个自由职业者的相同信息在相同的利基环境中出现了三次(行中相同的数据部分三次)：

freelancer_by_niche  
id | freelancer_id | niche_id  
 1 |       1       |    1    -- <-- same data (1, 1), different serial id
 2 |       1       |    1    -- <-- same data (1, 1), different serial id
 3 |       1       |    1    -- <-- same data (1, 1), different serial id

则上述查询的结果将返回每个可能的第3行(!)使用相同的(!)内容，因为freelancer_by_niche可以与所有其他JOIN组合三次。

您可以通过在上面的DISTINCT中使用SELECT DISTINCT a.id, ... FROM ...来消除重复项。如果您得到许多重复的行，例如，在5个JOIN表(freelancer_by_niche、freelancer_by_medium等)中的每个表中都有10个重复的数据，该怎么办？您将得到10 * 10 * 10 * 10 * 10 = 10 ^ 5 = 100000副本，它们都具有完全相同的信息。如果您要求您的数据库管理系统使用SELECT DISTINCT ...消除重复项，那么它必须对100000 duplicate rows per different row进行排序，因为只能通过排序(或散列，但没关系)来检测重复项。如果你有1000个不同的行供自由职业者在市场上，利基，语言等，那么你要求你的数据库管理系统排序1.000 * 100.000 = 100.000.000行，以减少到唯一的1000行的重复。那就是一亿行不必要的行。

请为freelancer_by_niche和其他JOIN表创建UNIQUE (freelancer_id, niche_id)。

(我所说的数据副本是指数据(niche_id, freelancer_id)是相同的，只有id是自动递增的串行。)

您可以通过执行以下操作轻松地重现该问题：

-- this duplicates all data of your JOIN tables once. Do it many times.
INSERT INTO freelancer_by_niche  
  SELECT (niche_id, freelancer_id) FROM freelancer_by_niche;  
INSERT INTO freelancer_by_medium  
  SELECT (medium_id, freelancer_id) FROM freelancer_by_medium;  
INSERT INTO freelancer_by_market  
  SELECT (market_id, freelancer_id) FROM freelancer_by_market;  
INSERT INTO freelancer_by_format  
  SELECT (format_id, freelancer_id) FROM freelancer_by_format;  
INSERT INTO freelancer_by_lang  
  SELECT (lang_id, freelancer_id) FROM freelancer_by_lang;  

使用以下命令显示副本

SELECT * FROM freelancer_by_lang;

现在试试SELECT * FROM freelancer INNER JOIN ...这件事。如果它仍然运行得很快，那么就一遍又一遍地执行所有的INSERT INTO freelancer_by_niche ...，直到永远都不能计算结果。(或者，您可以使用DISTINCT删除重复项)。

创建唯一的数据连接表

您可以防止连接表中出现重复项。移除id SERIAL PRIMARY KEY并将其替换为多属性主键(a，b)：

CREATE TABLE freelancer_by_niche (
   niche_id        int NOT NULL REFERENCES niche (niche_id),
   freelancer_id   int NOT NULL REFERENCES freelancer (freelancer_id), 
   PRIMARY KEY (freelancer_id, niche_id)
);

(将此应用于所有连接表)。PRIMARY KEY (freelancer_id, niche_id)将创建一个UNIQUE索引。这样您就不能插入重复的数据(尝试上面的INSERT%s，将被拒绝，因为信息已经存在一次。添加另一个时间不会添加更多的信息，并且会使您的查询运行时间变慢)。

在连接表的另一部分使用PRIMARY KEY (freelancer_id, niche_id)时，Postgres在这两个属性(列)上创建唯一索引。通过freelancer_id访问或JOINing是很快的，因为它是索引中的第一个。访问或JOINing到freelancer_by_niche.niche_id的速度会很慢(freelancer_by_niche上的全表扫描)。

因此，您还应该在这个表freelancer_by_niche中的第二部分niche_id上创建一个索引。

CREATE INDEX ON freelancer_by_niche (niche_id) ;

然后在niche_id上连接到这个表也会更快，因为它们是由索引加速的。索引使查询(通常)更快。

摘要

您有一个非常好的规范化数据库模式！非常好。但是可以做一些小的改进(见上)。