giannis christofakis's questions

Tenho que implementar um sistema de controle de acesso para uma empresa, que será capaz de reportar o acesso de cada funcionário em uma área específica do prédio para fornecer um relatório. Um dos requisitos é fornecer uma lista dos funcionários com mais de 3 horas extras em uma determinada janela de tempo.

O esquema da tabela que contém as informações necessárias é o seguinte. ACTIONinforma se o funcionário entrou ou saiu de um determinado nível do edifício. O resto é o tempo, o funcionário e o nível.

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)
);

um conjunto de resultados de amostra

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

Acredito que sei como posso estimar as horas extras considerando a soma do tempo que cada funcionário passou dentro das dependências do prédio que dará o total de horas. Mas eu só posso imaginar como eu poderia fazer isso na camada de aplicação.

Existe uma maneira de estimar as horas extras puramente com SQL?

EDITAR

De acordo com os comentários, deve haver uma sequência estrita entre as ações de entrar-sair, portanto, criou-se o seguinte conjunto de dados .

Alguém pode me explicar a diferença entre as duas perguntas a seguir? Apesar de parecerem iguais, os resultados são diferentes.

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 (..) 

Na segunda, o existspredicado é movido da connect bycláusula para a wherecláusula.

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;

Talvez o título seja enganoso, mas tentarei explicar o que estou tentando fazer.

Estou construindo um site de comércio eletrônico. Tenho meus produtos e meu pedido. Quero adicionar restrições de como o pedido será entregue ao cliente dependendo de cada produto.

Por exemplo, se eu vender um produto grande e frágil, quero restringir o envio por correio, o cliente deve ter apenas a opção de retirá-lo na loja. Ou um produto específico só pode ser enviado para países limitados.

Achei que poderia criar uma tabela extra relacionada a produtos.

Dessa forma, eu poderia saber quais tipos de restrições cada produto possui. E não tenho limitação de quantos tipos extras de restrição posso fazer.

Por exemplo

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

Mas se fizer isso,

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

o campo de valor é um tipo diferente.

Meu segundo pensamento foi usar um campo de restrições do tipo TEXT e passar dentro de ambos os valores e restrições em um JSONformato ouXML

Outro pensamento foi criar tabelas separadas para cada tipo,

e juntar todos eles, mas não parecia apropriado.

Qual seria o melhor no meu caso?
Existe uma maneira melhor de projetar meu banco de dados?

Eu tenho uma usersmesa. Quero acompanhar todas as alterações que fiz, nome, telefone etc. Pensei em criar uma tabela extra changesque armazenará a idversão anterior e a próxima do usuário. Eu mantenho todos os usersregistros. Será esta uma boa abordagem?

Atualizar

Também pensei em usar uma tabela separada para o histórico, veja abaixo

Ou, para usar uma única tabela e rastrear sua versão do timestampou do id(já que é incremento), mas considero isso uma solução ruim.

Eu tenho o esquema abaixo, os campos owner_id,billing_id,tech_id,admin_idna cartstabela estão apontando para a contactstabela. Como você pode ver no resultado da consulta, tenho que fazer isso JOINpara cada campo. Existe uma maneira de evitar?

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

Eu tenho uma domainstabela e cada domínio pertence a um arquivo contact. Além disso, você pode atribuir 3 extras contactscom diferentes funções admin , tech e billing , essas não são obrigatórias .

Meu primeiro pensamento foi adicionar três campos extras na minha tabela de domínios .

mas aí achei melhor criar uma tabela de interseção.

Alguma sugestão?

Eu tenho o seguinte projeto. Cada usuário tem seus próprios contatos , seus próprios hosts e os atribui a um domínio que pertence apenas a ele.

Quero garantir que o host que você atribui ao domínio pertence ao usuário ao qual o domínio está atribuído.

É algo que devo garantir na lógica do meu aplicativo?

Se eu definir domains.user_id foreign keycomo referência host.user_ide user_id.idisso eliminar meu problema?

A propósito, alguém pode me sugerir um design melhor para o meu esquema?

Estou tentando implementar a UML abaixo e não consigo descobrir onde está meu erro, pois já coloquei um valor na tabela.

-- 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';

O erro está relacionado à última parte do código.

ERRO: inserção ou atualização na tabela "container" viola a restrição de chave estrangeira "container_gobject_fkey" DETALHE: Chave (gobject)=(6) não está presente na tabela "graphobject".

********** Erro **********

ERRO: inserção ou atualização na tabela "container" viola a restrição de chave estrangeira "container_gobject_fkey" Estado SQL: 23503 Detalhe: Chave (gobject)=(6) não está presente na tabela "graphobject".

ATUALIZAR

OK, eu não entendi herança. Alguém pode me aconselhar como implementar a containsrelação entre GraphPanele GraphObject, Tree, Arrow, House.

eu tenho essas tres mesas

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 |
+---------+-------------------------------------------+--------------------+

Eu quero combinar as duas tabelas abaixo para ter uma com as Descrições de Ocupação, Cargo por Cargo e Usuários por Cargo.

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 |
+-----------------+-------+

Algo assim

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

Eu tenho as tabelas abaixo. A primeira tem postagens com uma chave estrangeira para uma tabela de categoria. Estou procurando uma consulta que me dê a quantidade de postagens em cada categoria.

A mesa de correio.

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

A tabela de categorias.

 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 |
+---------------------+--------+

Estou procurando uma consulta que me dê algo assim.

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