Geekly articles weekly

功能数据库管理系统

使用SQL语言的关系DBMS早已捕获了数据库的世界。 如此之多，以至于新兴的物种被称为NoSQL。 他们设法在这个市场上赢得了一定的位置，但是关系型DBMS不会消失，并且会继续积极地用于其目的。

在本文中，我想描述功能数据库的概念。 为了更好地理解，我将通过与经典关系模型进行比较来做到这一点。 例如，将使用Internet上各种SQL测试中的任务。

引言


关系数据库在表和字段上运行。 在功能数据库中，将使用类和函数代替。 具有N个键的表中的字段将作为N个参数的函数显示。 代替表之间的关系，将使用返回所链接类的对象的函数。 代替JOIN函数将被使用。

在直接进行任务之前，我将描述域逻辑的任务。 对于DDL，我将使用PostgreSQL语法。 对于功能，其自身的语法。

表格和字段


具有字段名称和价格的简单Sku对象：

关系型

CREATE TABLE Sku
(
    id bigint NOT NULL,
    name character varying(100),
    price numeric(10,5),
    CONSTRAINT id_pkey PRIMARY KEY (id)
)


CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);

, Sku, .

, , , .

/ / . , . , :



CREATE TABLE prices
(
    skuId bigint NOT NULL,
    storeId bigint NOT NULL,
    supplierId bigint NOT NULL,
    dateTime timestamp without time zone,
    price numeric(10,5),
    CONSTRAINT prices_pkey PRIMARY KEY (skuId, storeId, supplierId)
)


CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);


, .



CREATE INDEX prices_date
    ON prices
    (skuId, storeId, supplierId, dateTime)


INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);


, .

( ).

CLASS Department;
name = DATA STRING[100] (Department);

CLASS Employee;
department = DATA Department (Employee);
chief = DATA Employee (Employee);
name = DATA STRING[100] (Employee);
salary = DATA NUMERIC[14,2] (Employee);

1.1


, .



select a.*
from   employee a, employee b
where  b.id = a.chief_id
and    a.salary > b.salary


SELECT name(Employee a) WHERE salary(a) > salary(chief(a));

1.2


,



select a.*
from   employee a
where  a.salary = ( select max(salary) from employee b
                    where  b.department_id = a.department_id )


maxSalary ' ' (Department s) = 
    GROUP MAX salary(Employee e) IF department(e) = s;
SELECT name(Employee a) WHERE salary(a) = maxSalary(department(a));

//   ""
SELECT name(Employee a) WHERE 
    salary(a) = maxSalary(GROUP MAX salary(Employee e) IF department(e) = department(a));

. CREATE VIEW, . , .

1.3


ID , 3 .



select department_id
from   employee
group  by department_id
having count(*) <= 3


countEmployees ' ' (Department d) = 
    GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;

1.4


, , - .



select a.*
from   employee a
left   join employee b on (b.id = a.chief_id and b.department_id = a.department_id)
where  b.id is null


SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));

1.5


ID .



with sum_salary as
  ( select department_id, sum(salary) salary
    from   employee
    group  by department_id )
select department_id
from   sum_salary a       
where  a.salary = ( select max(salary) from sum_salary )


salarySum ' ' (Department d) = 
    GROUP SUM salary(Employee e) IF department(e) = d;
maxSalarySum '  ' () = 
    GROUP MAX salarySum(Department d);
SELECT Department d WHERE salarySum(d) = maxSalarySum();


. , MS SQL.

2.1


1997 30 №1?

( ):
CLASS Employee '';
lastName '' = DATA STRING[100] (Employee);

CLASS Product '';
id = DATA INTEGER (Product);
name = DATA STRING[100] (Product);

CLASS Order '';
date = DATA DATE (Order);
employee = DATA Employee (Order);

CLASS Detail ' ';

order = DATA Order (Detail);
product = DATA Product (Detail);
quantity = DATA NUMERIC[10,5] (Detail);



select LastName
from Employees as e
where (
  select sum(od.Quantity)
  from [Order Details] as od
  where od.ProductID = 1 and od.OrderID in (
    select o.OrderID
    from Orders as o
    where year(o.OrderDate) = 1997 and e.EmployeeID = o.EmployeeID)
) > 30


sold (Employee e, INTEGER productId, INTEGER year) = 
    GROUP SUM quantity(OrderDetail d) IF 
        employee(order(d)) = e AND 
        id(product(d)) = productId AND 
        extractYear(date(order(d))) = year;
SELECT lastName(Employee e) WHERE sold(e, 1, 1997) > 30;

2.2


(, ) (), 1997- .

:
CLASS Customer '';
contactName '' = DATA STRING[100] (Customer);

customer = DATA Customer (Order);

unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);



SELECT ContactName, ProductName FROM (
SELECT c.ContactName, p.ProductName
, ROW_NUMBER() OVER (
    PARTITION BY c.ContactName
    ORDER BY SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) DESC
) AS RatingByAmt
FROM Customers c
JOIN Orders o ON o.CustomerID = c.CustomerID
JOIN [Order Details] od ON od.OrderID = o.OrderID
JOIN Products p ON p.ProductID = od.ProductID
WHERE YEAR(o.OrderDate) = 1997
GROUP BY c.ContactName, p.ProductName
) t
WHERE RatingByAmt < 3


sum (Detail d) = quantity(d) * unitPrice(d) * (1 - discount(d));
bought '' (Customer c, Product p, INTEGER y) = 
    GROUP SUM sum(Detail d) IF 
        customer(order(d)) = c AND 
        product(d) = p AND 
        extractYear(date(order(d))) = y;
rating '' (Customer c, Product p, INTEGER y) = 
    PARTITION SUM 1 ORDER DESC bought(c, p, y), p BY c, y;
SELECT contactName(Customer c), name(Product p) WHERE rating(c, p, 1997) < 3;

PARTITION : , SUM ( 1), ( Customer Year, ), , ORDER ( bought, , ).

2.3


.

:
CLASS Supplier '';
companyName = DATA STRING[100] (Supplier);

supplier = DATA Supplier (Product);

unitsInStock '  ' = DATA NUMERIC[10,3] (Product);
reorderLevel ' ' = DATA NUMERIC[10,3] (Product);


select s.CompanyName, p.ProductName, sum(od.Quantity) + p.ReorderLevel — p.UnitsInStock as ToOrder
from Orders o
join [Order Details] od on o.OrderID = od.OrderID
join Products p on od.ProductID = p.ProductID
join Suppliers s on p.SupplierID = s.SupplierID
where o.ShippedDate is null
group by s.CompanyName, p.ProductName, p.UnitsInStock, p.ReorderLevel
having p.UnitsInStock < sum(od.Quantity) + p.ReorderLevel


orderedNotShipped ',   ' (Product p) = 
    GROUP SUM quantity(OrderDetail d) IF product(d) = p;
toOrder ' ' (Product p) = orderedNotShipped(p) + reorderLevel(p) - unitsInStock(p);
SELECT companyName(supplier(Product p)), name(p), toOrder(p) WHERE toOrder(p) > 0;


. . . :
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);

. A, B, C , A B, B C, A C, A C.
:
SELECT Person a, Person b, Person c WHERE 
    likes(a, c) AND NOT friends(a, c) AND 
    friends(a, b) AND friends(b, c);

SQL. , , . . . . :
SELECT Person a, Person b, Person c WHERE 
    likes(a, c) AND NOT friends(a, c) AND 
    (friends(a, b) OR friends(b, a)) AND 
    (friends(b, c) OR friends(c, b));

UPD: dss_kalika:
SELECT 
   pl.PersonAID
  ,pf.PersonAID
  ,pff.PersonAID
FROM Persons                 AS p
--                      
JOIN PersonRelationShip      AS pl ON pl.PersonAID = p.PersonID
                                  AND pl.Relation  = 'Like'
--                     
JOIN PersonRelationShip      AS pf ON pf.PersonAID = p.PersonID 
                                  AND pf.Relation = 'Friend'
--               
JOIN PersonRelationShip      AS pff ON pff.PersonAID = pf.PersonBID
                                   AND pff.PersonBID = pl.PersonBID
                                   AND pff.Relation = 'Friend'
--           
LEFT JOIN PersonRelationShip AS pnf ON pnf.PersonAID = p.PersonID
                                   AND pnf.PersonBID = pff.PersonBID
                                   AND pnf.Relation = 'Friend'
WHERE pnf.PersonAID IS NULL 


;WITH PersonRelationShipCollapsed AS (
  SELECT pl.PersonAID
        ,pl.PersonBID
        ,pl.Relation 
  FROM #PersonRelationShip      AS pl 
  
  UNION 

  SELECT pl.PersonBID AS PersonAID
        ,pl.PersonAID AS PersonBID
        ,pl.Relation
  FROM #PersonRelationShip      AS pl 
)
SELECT 
   pl.PersonAID
  ,pf.PersonBID
  ,pff.PersonBID
FROM #Persons                      AS p
--                      
JOIN PersonRelationShipCollapsed  AS pl ON pl.PersonAID = p.PersonID
                                 AND pl.Relation  = 'Like'                                  
--                          
JOIN PersonRelationShipCollapsed  AS pf ON pf.PersonAID = p.PersonID 
                                 AND pf.Relation = 'Friend'
--                    
JOIN PersonRelationShipCollapsed  AS pff ON pff.PersonAID = pf.PersonBID
                                 AND pff.PersonBID = pl.PersonBID
                                 AND pff.Relation = 'Friend'
--                     
LEFT JOIN PersonRelationShipCollapsed AS pnf ON pnf.PersonAID = p.PersonID
                                   AND pnf.PersonBID = pff.PersonBID
                                   AND pnf.Relation = 'Friend'
WHERE pnf.[PersonAID] IS NULL



, — . SQL, , . , - , . — . C++, Python .

, :

  • . , . (, ), , , .
  • . (, sold, bought ..), . . , , sold , . , CREATE VIEW. , .
  • . ( ). , ( , — , ). , «» . , . , :
    CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = '' MESSAGE  '-        2019 ';

  • . CLASS ClassP: Class1, Class2 . , .

, , Java, . , . , ( PostgreSQL) « ». , , . , , , .

Source: https://habr.com/ru/post/zh-CN458774/

More articles:


All Articles