Functional DBMS

Lub ntiaj teb ntawm cov ntaub ntawv tau ntev tau tswj hwm los ntawm kev sib raug zoo DBMSs, uas siv cov lus SQL. Ntau npaum li cov kev hloov pauv tshiab hu ua NoSQL. Lawv tau tswj xyuas qhov chaw rau lawv tus kheej hauv kev ua lag luam no, tab sis kev sib raug zoo DBMSs yuav tsis tuag, thiab txuas ntxiv mus siv rau lawv lub hom phiaj.

Hauv tsab xov xwm no kuv xav piav qhia txog lub tswv yim ntawm kev ua haujlwm database. Rau kev nkag siab zoo dua, kuv yuav ua qhov no los ntawm kev sib piv nrog cov qauv kev sib raug zoo classical. Teeb meem los ntawm ntau yam kev xeem SQL pom hauv Is Taws Nem yuav raug siv ua piv txwv.

Taw qhia

Relational databases ua haujlwm ntawm rooj thiab teb. Hauv cov ntaub ntawv siv tau, cov chav kawm thiab cov haujlwm yuav raug siv hloov, raws li. Ib daim teb nyob rau hauv ib lub rooj nrog N yuam sij yuav sawv cev raws li ib tug muaj nuj nqi ntawm N tsis. Hloov chaw ntawm kev sib raug zoo ntawm cov ntxhuav, cov haujlwm yuav raug siv uas rov qab cov khoom ntawm cov chav kawm uas qhov kev sib txuas tau ua. Muaj nuj nqi muaj pes tsawg leeg yuav raug siv los ntawm JOIN.

Ua ntej txav ncaj qha mus rau cov dej num, kuv yuav piav qhia txog txoj haujlwm ntawm kev sau logic. Rau DDL Kuv yuav siv PostgreSQL syntax. Rau functional nws muaj nws tus kheej syntax.

Rooj thiab teb

Ib qho yooj yim Sku khoom nrog lub npe thiab nqi teb:

Kev txheeb ze

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

ua haujlwm

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


Peb tshaj tawm ob zog, uas siv ib qho parameter Sku raws li cov tswv yim thiab xa rov qab hom qub.

Nws yog assumed tias nyob rau hauv ib tug haumxeeb DBMS txhua yam khoom yuav muaj ib co sab hauv code uas yog cia li generated thiab tuaj yeem nkag tau yog tias tsim nyog.

Cia peb teeb tus nqi rau cov khoom / khw / chaw muag khoom. Tej zaum nws yuav hloov lub sij hawm, yog li cia peb ntxiv ib lub sij hawm teb rau lub rooj. Kuv yuav hla kev tshaj tawm cov lus rau cov npe hauv cov ntaub ntawv sib txheeb kom luv cov cai:

Kev txheeb ze

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

ua haujlwm

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


Indexes

Rau qhov piv txwv kawg, peb yuav tsim qhov ntsuas ntawm txhua tus yuam sij thiab hnub kom peb tuaj yeem nrhiav tus nqi sai rau lub sijhawm tshwj xeeb.

Kev txheeb ze

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

ua haujlwm

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


paub tab

Cia peb pib nrog cov teeb meem yooj yim coj los ntawm qhov sib thooj lus ntawm Habr.

Ua ntej, cia peb tshaj tawm cov logic sau npe (rau cov ntaub ntawv sib raug zoo no yog ua ncaj qha rau hauv kab lus saum toj no).

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


Ua haujlwm 1.1

Tso cov npe ntawm cov neeg ua haujlwm uas tau txais nyiaj hli ntau dua li ntawm lawv tus thawj saib xyuas tam sim ntawd.

Kev txheeb ze

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

ua haujlwm

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


Ua haujlwm 1.2

Sau cov neeg ua haujlwm uas tau txais nyiaj hli siab tshaj plaws hauv lawv chav haujlwm

Kev txheeb ze

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

ua haujlwm

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


Ob qhov kev siv yog sib npaug. Rau thawj rooj plaub, nyob rau hauv cov ntaub ntawv sib raug zoo koj tuaj yeem siv CREATE VIEW, uas tib txoj kev yuav ua ntej xam cov nyiaj hli siab tshaj plaws rau ib chav haujlwm tshwj xeeb hauv nws. Hauv dab tsi hauv qab no, kom pom tseeb, kuv yuav siv thawj rooj plaub, vim nws zoo dua qhia txog kev daws teeb meem.

Ua haujlwm 1.3

Muab cov npe ntawm cov chaw ua haujlwm ID, cov neeg ua haujlwm uas tsis pub tshaj 3 tus neeg.

Kev txheeb ze

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

ua haujlwm

countEmployees 'Количество сотрудников' (Department d) = 
    GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;


Ua haujlwm 1.4

Tso tawm cov npe ntawm cov neeg ua haujlwm uas tsis muaj tus thawj tswj hwm uas ua haujlwm hauv tib lub tuam tsev.

Kev txheeb ze

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

ua haujlwm

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


Ua haujlwm 1.5

Nrhiav cov npe ntawm department IDs nrog cov nyiaj hli siab tshaj plaws ntawm cov neeg ua haujlwm.

Kev txheeb ze

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 )

ua haujlwm

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


Cia peb txav mus rau ntau txoj haujlwm nyuaj los ntawm lwm qhov lus. Nws muaj cov ncauj lus kom ntxaws txog kev siv cov haujlwm no hauv MS SQL.

Ua haujlwm 2.1

Cov neeg muag khoom twg muag ntau tshaj 1997 units ntawm cov khoom No.30 hauv 1?

Domain logic (raws li ua ntej ntawm RDBMS peb hla cov lus tshaj tawm):

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


Kev txheeb ze

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

ua haujlwm

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;


Ua haujlwm 2.2

Rau txhua tus neeg yuav khoom (npe, npe), nrhiav ob yam khoom (npe) uas tus neeg yuav khoom siv nyiaj ntau tshaj plaws hauv xyoo 1997.

Peb txuas ntxiv lub logic ntawm cov piv txwv yav dhau los:

CLASS Customer 'Клиент';
contactName 'ФИО' = DATA STRING[100] (Customer);

customer = DATA Customer (Order);

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


Kev txheeb ze

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

ua haujlwm

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;


Tus neeg teb xov tooj PARTITION ua haujlwm raws li cov hauv qab no: nws suav cov lus qhia tom qab SUM (ntawm no 1), nyob rau hauv cov pab pawg tau teev tseg (ntawm no Cov Neeg Siv Khoom thiab Xyoo, tab sis tuaj yeem yog ib qho kev qhia), sorting hauv cov pab pawg los ntawm cov kab lus teev tseg hauv ORDER ( ntawm no yuav, thiab yog tias sib npaug, ces raws li cov khoom siv sab hauv).

Ua haujlwm 2.3

Muaj pes tsawg cov khoom yuav tsum tau xaj los ntawm cov neeg muag khoom kom ua tiav cov kev txiav txim tam sim no.

Cia peb nthuav lub ntsiab lus ntxiv dua:

CLASS Supplier 'Поставщик';
companyName = DATA STRING[100] (Supplier);

supplier = DATA Supplier (Product);

unitsInStock 'Остаток на складе' = DATA NUMERIC[10,3] (Product);
reorderLevel 'Норма продажи' = DATA NUMERIC[10,3] (Product);


Kev txheeb ze

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

ua haujlwm

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;


Teeb meem nrog lub hnub qub

Thiab qhov piv txwv kawg yog los ntawm kuv tus kheej. Muaj lub logic ntawm lub social network. Tib neeg tuaj yeem ua phooj ywg nrog ib leeg thiab nyiam ib leeg. Los ntawm kev ua haujlwm database foundations nws yuav zoo li no:

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


Nws yog qhov tsim nyog los nrhiav cov neeg sib tw ua phooj ywg. Ua ntej tshaj plaws, koj yuav tsum nrhiav txhua tus neeg A, B, C xws li A yog phooj ywg nrog B, thiab B yog phooj ywg nrog C, A nyiam C, tab sis A tsis yog phooj ywg nrog C.
Los ntawm kev ua haujlwm database foundations, cov lus nug yuav zoo li no:

SELECT Person a, Person b, Person c WHERE 
    likes(a, c) AND NOT friends(a, c) AND 
    friends(a, b) AND friends(b, c);


Tus nyeem ntawv txhawb kom daws tau qhov teeb meem no hauv SQL ntawm nws tus kheej. Nco ntsoov tias muaj cov phooj ywg tsawg dua li cov neeg koj nyiam. Yog li ntawd lawv nyob hauv cov rooj sib cais. Yog tias ua tiav, kuj tseem muaj txoj haujlwm nrog ob lub hnub qub. Nyob rau hauv nws, kev phooj ywg tsis yog symmetrical. Nyob rau hauv lub functional database nws yuav zoo li no:

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: kev daws teeb meem nrog lub hnub qub thawj thiab thib ob los ntawm dss_kali:

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 

xaus

Nws yuav tsum tau muab sau tseg tias cov lus syntax tsuas yog ib qho ntawm cov kev xaiv rau kev siv lub tswv yim muab. SQL tau coj los ua lub hauv paus, thiab lub hom phiaj yog rau nws kom zoo ib yam li qhov ua tau rau nws. Tau kawg, qee tus yuav tsis nyiam cov npe ntawm cov ntsiab lus, cov ntawv sau npe, thiab lwm yam. Qhov tseem ceeb ntawm no yog lub tswvyim nws tus kheej. Yog xav tau, koj tuaj yeem ua C ++ thiab Python zoo sib xws syntax.

Cov ntsiab lus piav qhia database, hauv kuv lub tswv yim, muaj qhov zoo hauv qab no:

• Qhov Yooj Yim. Qhov no yog ib qho kev qhia tseem ceeb uas tsis pom tseeb hauv cov xwm txheej yooj yim. Tab sis yog tias koj saib cov teeb meem nyuaj (piv txwv li, teeb meem nrog lub hnub qub), hauv kuv lub tswv yim, sau cov lus nug no yooj yim dua.
• Tshawb nrhiav. Hauv qee qhov piv txwv kuv tau tshaj tawm cov haujlwm nruab nrab (piv txwv li, muag, yuav thiab lwm yam), los ntawm cov haujlwm tom ntej tau tsim. Qhov no tso cai rau koj hloov lub logic ntawm qee yam haujlwm, yog tias tsim nyog, tsis tas yuav hloov lub logic ntawm cov uas nyob ntawm lawv. Piv txwv li, koj tuaj yeem ua kev muag khoom muag raug xam los ntawm cov khoom sib txawv kiag li, thaum tus so ntawm cov logic yuav tsis hloov. Yog lawm, qhov no tuaj yeem ua tiav hauv RDBMS siv CREATE VIEW. Tab sis yog hais tias tag nrho cov logic yog sau li no, nws yuav tsis zoo nyeem heev.
• Tsis muaj qhov sib txawv ntawm semantic. Cov ntaub ntawv zoo li no ua haujlwm ntawm cov haujlwm thiab cov chav kawm (tsis yog cov rooj thiab cov teb). Ib yam li nyob rau hauv classical programming (yog hais tias peb xav hais tias ib txoj kev yog ib tug muaj nuj nqi nrog rau cov thawj parameter nyob rau hauv daim ntawv ntawm cov chav kawm ntawv uas nws belongs). Raws li, nws yuav tsum yooj yim dua rau "ua phooj ywg" nrog cov lus programming thoob ntiaj teb. Tsis tas li ntawd, lub tswv yim no tso cai rau kev siv ntau yam kev ua haujlwm nyuaj. Piv txwv li, koj tuaj yeem embed tus neeg teb xov tooj xws li:

  CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'Петя' MESSAGE  'Что-то Петя продает слишком много одного товара в 2019 году';


• Keeb kwm thiab polymorphism. Hauv cov ntaub ntawv ua haujlwm, koj tuaj yeem qhia ntau yam qub txeeg qub teg los ntawm CLASS ClassP: Class1, Class2 tsim thiab siv ntau yam polymorphism. Tej zaum kuv yuav sau raws nraim li cas hauv cov ntawv yav tom ntej.

Txawm hais tias qhov no tsuas yog lub tswv yim xwb, peb twb muaj qee qhov kev siv hauv Java uas txhais tag nrho cov kev ua haujlwm rau hauv kev sib raug zoo logic. Ntxiv rau, lub logic ntawm cov neeg sawv cev thiab ntau ntau lwm yam zoo nkauj txuas rau nws, ua tsaug uas peb tau txais tag nrho. platform. Qhov tseem ceeb, peb siv RDBMS (tsuas yog PostgreSQL rau tam sim no) ua "lub tshuab virtual". Cov teeb meem qee zaum tshwm sim nrog qhov kev txhais lus no vim RDBMS cov lus nug optimizer tsis paub qee yam txheeb cais uas FDBMS paub. Nyob rau hauv txoj kev xav, nws muaj peev xwm los siv ib qho kev tswj hwm database uas yuav siv qee cov qauv raws li kev khaws cia, hloov kho tshwj xeeb rau kev ua haujlwm logic.

Tau qhov twg los: www.hab.com