giannis christofakis's questions

我必须为一家公司实施访问控制系统，该系统将能够报告建筑物特定区域中每个员工的访问情况，以便提供报告。其中一项要求是提供在给定日期时间窗口内加班时间超过 3 小时的员工名单。

包含所需信息的表的表架构如下。 ACTION说明员工是否进入或离开建筑物的特定楼层。剩下的就是时间、员工和水平。

create table access_action
(
    ID          bigint auto_increment primary key,
    ACTION      int      null,
    TIMESTAMP   datetime null,
    level_id    bigint   null,
    employee_id bigint   null,
    constraint FK_access_action_employee_id
        foreign key (employee_id) references employee (ID),
    constraint FK_access_action_level_id
        foreign key (level_id) references level (ID)
);

样本结果集

ID  ACTION      TIMESTAMP       EMPLOYEE_ID
1   IN  2017-05-10 09:28:00 3   9
2   IN  2017-04-24 07:49:00 3   8
3   IN  2017-02-27 07:08:00 1   9
4   IN  2017-04-26 06:15:00 1   25
5   IN  2017-02-02 09:37:00 3   24
6   OUT 2017-05-29 08:03:00 3   7
7   IN  2017-04-25 07:07:00 1   1
8   IN  2017-01-09 08:54:00 3   8
9   IN  2017-05-12 07:57:00 2   1
10  OUT 2017-02-09 08:03:00 3   30
11  IN  2017-03-09 08:56:00 3   30
12  OUT 2017-02-03 11:26:00 3   5

我相信我知道如何估计加班时间，考虑到每个员工在建筑物内花费的时间总和，这将给出总小时数。但我只能想象如何在应用层做到这一点。

有没有办法可以纯粹用 SQL 来估计加班时间？

编辑

根据评论，进出动作之间必须有严格的顺序，因此创建了以下数据集。

谁能向我解释以下两个查询之间的区别？尽管它们看起来相同，但结果却不同。

select concept.concept_id, concept.PARENT_ID 
from ebti_thes_concept_v concept
  start with PARENT_ID = '11025'
  connect by parent_id = prior concept_id
  and exists (..) 

在第二个中，exists谓词从connect by子句移动到where子句。

select concept.concept_id, concept.PARENT_ID
from ebti_thes_concept_v concept
where exists (..)
 start with PARENT_ID = '11025'
 connect by parent_id = prior concept_id;

也许标题具有误导性，但我会尝试解释我正在尝试做的事情。

我正在建立一个电子商务网站。我有我的产品和订单。我想根据每种产品对订单如何交付给客户添加限制。

例如，如果我出售一件大而易碎的产品，我想限制它不能通过快递发送，那么客户应该只能选择从商店取走。或者特定产品只能发送到有限的国家/地区。

我想我可以创建一个与产品相关的额外表格。

这样我就可以知道每个产品有哪些类型的约束。而且我对我可以做多少额外类型的限制没有限制。

例如

restrictions(1,124,'post.courier',false)

但如果这样做，

restrictions(2,1322,'country','Greece')

值字段是不同的类型。

我的第二个想法是使用字段限制类型 TEXT 并以JSON格式或XML

另一个想法是为每种类型创建单独的表，

加入他们，但这似乎不合适。

在我的情况下什么是最好的？
有没有更好的方法可以设计我的数据库？

我有一张users桌子。我想跟踪我所做的所有更改、姓名、电话等。我想创建一个额外的表changes来存储id用户的上一个和下一个版本。我保留所有users记录。这是一个好方法吗？

更新

我还想为历史使用单独的表格，请参见下文

或者，使用单个表并从timestamp或跟踪其版本id（因为它是增量的），但我认为这是一个糟糕的解决方案。

我有以下架构，表上的字段owner_id,billing_id,tech_id,admin_id指向carts表contacts。正如您从查询结果中看到的那样，我必须JOIN为每个字段。有没有办法避免？

SELECT c.domain, c.period, co.name AS owner, co1.name AS admin, co2.name AS tech, co3.name AS billing, h.nameserver
FROM carts c
LEFT JOIN contacts co ON c.owner_id = co.id
LEFT JOIN contacts co1 ON c.admin_id = co1.id
LEFT JOIN contacts co2 ON c.tech_id = co2.id
LEFT JOIN contacts co3 ON c.billing_id = co3.id
LEFT JOIN HOSTS h ON c.host_id = h.id
WHERE c.user_id =1

我有一张domains表，每个域都属于一个contact. 此外，您还可以分配 3 个contacts具有不同角色的admin、tech和billing，这些不是强制性的。

我的第一个想法是在我的域表中添加三个额外的字段。

但后来我认为创建一个交集表更好。

有什么建议么？

我有以下设计。每个用户都有自己的联系人，自己的主机，并将它们分配给只属于他的域。

我想确保您分配给域的主机属于该域分配给的用户。

这是我必须在我的应用程序逻辑中确保的东西吗？

如果我设置domains.user_id foreign key为参考host.user_id，user_id.id它会消除我的问题吗？

顺便说一句，谁能建议我更好地设计我的架构？

我正在尝试实现下面的 UML，但我无法弄清楚我的错误在哪里，因为我已经在表中放置了一个值。

-- Schema: public

DROP SCHEMA IF EXISTS public CASCADE;

CREATE SCHEMA public
  AUTHORIZATION postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public IS 'standard public schema';

CREATE TYPE color AS ENUM ('green','black','yellow','grey','blue');
CREATE TYPE arrowtype AS ENUM ('both_arrows','begin_arrow','end_arrow'); 
CREATE TYPE treetype AS ENUM ('orange_tree','apple_tree');
CREATE TYPE linetype AS ENUM ('hairline','solid','dotted','dashed');

CREATE TYPE address AS (
street_name TEXT,
street_no TEXT
);

CREATE TYPE square AS (
row VARCHAR(10),
colmn VARCHAR(10)
);

CREATE TABLE user_table (
username VARCHAR(30) NOT NULL PRIMARY KEY,
password VARCHAR(16),
name     VARCHAR(100),
lastname VARCHAR(100),
telephone VARCHAR(20)[],
address address,
birth_year INTEGER
);

CREATE TABLE GraphPanel (
id SERIAL NOT NULL PRIMARY KEY,
title VARCHAR(255),
creation_date TIMESTAMP,
comments TEXT
);

CREATE TABLE GraphObject (
id SERIAL NOT NULL PRIMARY KEY,
color color,
title VARCHAR(255)
);

CREATE TABLE House (
roof_color color,
roof_height INTEGER,
position square
)INHERITS(GraphObject);

CREATE TABLE Arrow (
arrow_line linetype,
arrow_type arrowtype
)INHERITS(GraphObject);

CREATE TABLE Tree (
position square,
tree_type treetype
)INHERITS(GraphObject);

CREATE TABLE creator (
username VARCHAR(30),
panel INTEGER,
category VARCHAR(50),
FOREIGN KEY (username) REFERENCES user_table(username),
FOREIGN KEY (panel) REFERENCES GraphPanel(id)
);

CREATE TABLE container (
panel  INTEGER,
gobject INTEGER,
FOREIGN KEY (panel) REFERENCES GraphPanel(id),
FOREIGN KEY (gobject) REFERENCES GraphObject(id)
);

-- INSERTING VALUES 

-- Users

INSERT INTO user_table (
    username,
    password,name,
    lastname,
    telephone,
    address,
    birth_year
) VALUES (
    'giannis',
    'thepassword',
    'Giannis',
    'Christofakis',
    '{"6944789333","2831051300"}',
    ROW('Evridikis','7'),
    1985
);

INSERT INTO user_table (
    username,
    password,
    name,
    lastname,
    telephone,
    address,
    birth_year
) VALUES (
    'antonis',
    'psw',
    'Antonis',
    'Papadakis',
    '{"6974246700","2831051312"}',
    ROW('Doukos Mpofor','12'),
    1993
);

INSERT INTO user_table (
    username,
    password,
    name,
    lastname,
    telephone,
    address,
    birth_year
) VALUES (
    'mpampis',
    'thepsw',
    'Mpampis',
    'Theodorou',
    '{"6984652333","284131100"}',
    ROW('Markoy Portaliou','8'),
    1981
);

-- SELECT * FROM user_table;

-- GraphPanels

INSERT INTO GraphPanel (
    title,
    creation_date,
    comments
) VALUES (
    'Platia Sinani',
    '2004-10-19 10:23:54',
    'Diamorfosi ths platias Sinani'
);

INSERT INTO GraphPanel (
    title,
    creation_date,
    comments
) VALUES (
    'Parking TEI',
    '2008-11-04 22:20:01',
    'Apeikonisi parking tou TEI'
);

INSERT INTO GraphPanel (
    title,
    creation_date,
    comments
) VALUES (
    'Perioxh Stavormenou',
    '2011-05-17 18:23:33',
    'Dimhourgeia parkou sth perioxh tou Estavromenou'
);

INSERT INTO GraphPanel (
    title,
    creation_date,
    comments
) VALUES (
    'Odos Panagiotaki',
    '2000-09-22 11:00:33',
    'Kataskeuh gefuras'
);

-- SELECT * FROM GraphPanel;

-- creator

INSERT INTO creator (
    username,
    panel,
    category
) VALUES (
    'giannis',
     1,
    'Diamorfosh'
);

INSERT INTO creator (
    username,
    panel,
    category
) VALUES (
    'mpampis',
     2,
    'Apeikonish'
);

INSERT INTO creator (
    username,
    panel,
    category
) VALUES (
    'antonis',
     3,
    'Kataskeuh'
);

INSERT INTO creator (
    username,
    panel,
    category
) VALUES (
    'giannis',
     4,
    'Kataskeuh'
);

-- SELECT * FROM creator;

-- GraphObjects

-- House

INSERT INTO HOUSE (
    color,
    title,
    roof_color,
    roof_height,
    position
) VALUES (
    'grey',
    'Ate Bank',
    'green',
     6,
     ROW('12','A3')
);

INSERT INTO HOUSE (
    color,
    title,
    roof_color,
    roof_height,
    position
) VALUES (
    'grey',
    'residents house',
    'blue',
     4,
     ROW('23','Y3')
);

INSERT INTO HOUSE (
    color,
    title,
    roof_color,
    roof_height,
    position
) VALUES (
    'blue',
    'Super Market',
    'yellow',
     7,
     ROW('08','L2')
);

-- SELECT * FROM House;

-- Trees

INSERT INTO Tree (
    color,
    title,
    position,
    tree_type
) VALUES (
    'green',
    'valencia',
     ROW('07','L2'),
     'orange_tree'
);

INSERT INTO Tree (
    color,
    title,
    position,
    tree_type
) VALUES (
    'green',
    'mantarinia',
     ROW('18','J2'),
     'orange_tree'
);

INSERT INTO Tree (
    color,
    title,
    position,
    tree_type
) VALUES (
    'green',
    'firikia',
     ROW('03','E1'),
     'apple_tree'
);

-- SELECT * FROM Tree;

INSERT INTO Arrow (
    color,
    title,
    arrow_line,
    arrow_type
) VALUES (
    'black',
    'starting point',
     'solid',
     'begin_arrow'
);

INSERT INTO Arrow (
    color,
    title,
    arrow_line,
    arrow_type
) VALUES (
    'grey',
    'ending way',
     'dashed',
     'end_arrow'
);

INSERT INTO Arrow (
    color,
    title,
    arrow_line,
    arrow_type
) VALUES (
    'black',
    'both ways',
     'dotted',
     'both_arrows'
);

 SELECT * FROM GraphObject order by id;

-- container



INSERT INTO container (
    panel,
    gobject
) VALUES (
     1,
     6
);


SELECT * FROM container;

-- SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

该错误与代码的最后一部分有关。

错误：在表“container”上插入或更新违反了外键约束“container_gobject_fkey”详细信息：表“graphobject”中不存在键（gobject）=（6）。

********** 错误 **********

错误：在表“容器”上插入或更新违反外键约束“container_gobject_fkey”SQL 状态：23503 详细信息：表“graphobject”中不存在键 (gobject)=(6)。

更新

好的，我误解了继承。任何人都可以建议我如何实现和, , ,contains之间的关系。GraphPanelGraphObjectTreeArrowHouse

我有那三张桌子

SELECT occuId as 'Occupation Id',occuDscr as 'Occupation Description' FROM occupation_field;
+---------------+------------------------+
| Occupation Id | Occupation Description |
+---------------+------------------------+
|             1 | Software Engineer      |
|             2 | Economics              |
|             3 | Structural Engineer    |
|             4 | Legal Advisors         |
|             5 | Plumbers               |
|             6 | Social Advisors        |
|             7 | Musicians              |
+---------------+------------------------+

SELECT usrId AS 'User Id',usrName AS 'User Name',usrOccuId AS 'User Occupation Id' FROM users;
+---------+------------+--------------------+
| User Id | User Name  | User Occupation Id |
+---------+------------+--------------------+
|       2 | goodys     |                  6 |
|       5 | realmadrid |                  7 |
|       6 | petousis   |                  6 |
+---------+------------+--------------------+

SELECT pstId AS 'Post Id',pstTitle AS 'Post Title',pstOccuId AS 'Post Occupation Id' FROM job_post ORDER BY pstId;
+---------+-------------------------------------------+--------------------+
| Post Id | Post Title                                | Post Occupation Id |
+---------+-------------------------------------------+--------------------+
|       4 | Software Engineer Recruit                 |                  1 |
|       5 | Web Developer Recruit                     |                  1 |
|       6 | Saxophonist                               |                  7 |
|       7 | Construction Company looking for plumber. |                  5 |
|       8 | Economic Analyst                          |                  2 |
|       9 | Legal Advisor                             |                  4 |
|      10 | Economic Assistant                        |                  2 |
+---------+-------------------------------------------+--------------------+

我想将下面的两个表结合起来，以便有一个包含职业描述、每个职位的帖子和每个职位的用户。

SELECT occuDscr job,count(pstOccuId) AS 'Posts' FROM job_post INNER JOIN occupation_field on pstOccuId = occuId GROUP BY job;
+-------------------+-------+
| job               | Posts |
+-------------------+-------+
| Economics         |     2 |
| Legal Advisors    |     1 |
| Musicians         |     1 |
| Plumbers          |     1 |
| Software Engineer |     2 |
+-------------------+-------+

SELECT occuDscr job,count(usrOccuId) AS 'Users' FROM users INNER JOIN occupation_field on usrOccuId = occuId GROUP BY job;
+-----------------+-------+
| job             | Users |
+-----------------+-------+
| Musicians       |     1 |
| Social Advisors |     2 |
+-----------------+-------+

像这样的东西

    +-------------------+-------+-------+
    | job               | Posts | Users |
    +-------------------+-------+-------+
    | Economics         |     2 |     0 |
    | Legal Advisors    |     1 |     0 |
    | Musicians         |     1 |     1 |
    | Plumbers          |     1 |     0 |
    | Software Engineer |     2 |     0 |
    | Social Advisors   |     0 |     1 |
    +-------------------+-------+-------+

我有以下表格。第一个表格的帖子带有类别表的外键。我正在寻找一个查询，该查询可为我提供每个类别的帖子数量。

帖子表。

 select pstOccuId,pstTitle from job_post;
+-----------+-------------------------------------------+
| pstOccuId | pstTitle                                  |
+-----------+-------------------------------------------+
|         1 | Software Engineer Recruit                 |
|         1 | Web Developer Recruit                     |
|         7 | Saxophonist                               |
|         5 | Construction Company looking for plumber. |
+-----------+-------------------------------------------+

类别表。

 select occuDscr,occuId from occupation_field;
+---------------------+--------+
| occuDscr            | occuId |
+---------------------+--------+
| Software Engineer   |      1 |
| Economics           |      2 |
| Structural Engineer |      3 |
| Legal Advisors      |      4 |
| Plumbers            |      5 |
| Social Advisors     |      6 |
| Musicians           |      7 |
+---------------------+--------+

我正在寻找一个能给我这样的查询。

+---------------------+--------+
| occuDscr            | amount |
+---------------------+--------+
| Software Engineer   |      2 |
| Plumbers            |      1 |
| Musicians           |      1 |
+---------------------+--------+