DBMS inayofanya kazi

Ulimwengu wa hifadhidata kwa muda mrefu umetawaliwa na DBMS za uhusiano, ambazo hutumia lugha ya SQL. Kiasi kwamba lahaja zinazoibuka zinaitwa NoSQL. Waliweza kujitengenezea mahali fulani katika soko hili, lakini DBMS za uhusiano hazitakufa, na zinaendelea kutumika kikamilifu kwa madhumuni yao.

Katika makala hii nataka kuelezea dhana ya database ya kazi. Kwa ufahamu bora, nitafanya hivyo kwa kulinganisha na mfano wa classical wa uhusiano. Matatizo kutoka kwa majaribio mbalimbali ya SQL yanayopatikana kwenye Mtandao yatatumika kama mifano.

Utangulizi

Hifadhidata za uhusiano hufanya kazi kwenye meza na uwanja. Katika hifadhidata ya kazi, madarasa na kazi zitatumika badala yake, mtawalia. Sehemu katika jedwali iliyo na vitufe vya N itawakilishwa kama kazi ya vigezo vya N. Badala ya uhusiano kati ya meza, kazi zitatumika ambazo zinarudisha vitu vya darasa ambalo unganisho hufanywa. Utunzi wa chaguo za kukokotoa utatumika badala ya JIUNGE.

Kabla ya kuhamia moja kwa moja kwenye kazi, nitaelezea kazi ya mantiki ya kikoa. Kwa DDL nitatumia syntax ya PostgreSQL. Kwa utendakazi ina syntax yake.

Majedwali na mashamba

Kitu rahisi cha Sku chenye jina na uwanja wa bei:

Kimahusiano

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

kazi

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


Tunatangaza mbili kazi, ambayo huchukua kigezo kimoja cha Sku kama pembejeo na kurudisha aina ya awali.

Inachukuliwa kuwa katika DBMS inayofanya kazi kila kitu kitakuwa na msimbo fulani wa ndani ambao hutolewa kiotomatiki na unaweza kufikiwa ikiwa ni lazima.

Hebu tuweke bei ya bidhaa/duka/msambazaji. Huenda ikabadilika baada ya muda, kwa hivyo hebu tuongeze uga wa saa kwenye jedwali. Nitaruka kutangaza meza za saraka katika hifadhidata ya uhusiano ili kufupisha nambari:

Kimahusiano

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

kazi

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


Faharisi

Kwa mfano wa mwisho, tutaunda faharisi kwenye funguo zote na tarehe ili tuweze kupata haraka bei kwa wakati maalum.

Kimahusiano

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

kazi

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


kazi

Wacha tuanze na shida rahisi zilizochukuliwa kutoka kwa zinazolingana nakala juu ya Habr.

Kwanza, hebu tutangaze mantiki ya kikoa (kwa hifadhidata ya uhusiano hii inafanywa moja kwa moja kwenye kifungu hapo juu).

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


Jukumu la 1.1

Onyesha orodha ya wafanyikazi wanaopokea mshahara mkubwa kuliko ule wa msimamizi wao wa karibu.

Kimahusiano

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

kazi

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


Jukumu la 1.2

Orodhesha wafanyikazi wanaopokea mishahara ya juu zaidi katika idara zao

Kimahusiano

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

kazi

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


Utekelezaji wote wawili ni sawa. Kwa kesi ya kwanza, katika hifadhidata ya uhusiano unaweza kutumia CREATE VIEW, ambayo kwa njia hiyo hiyo itahesabu kwanza mshahara wa juu kwa idara maalum ndani yake. Katika kile kinachofuata, kwa uwazi, nitatumia kesi ya kwanza, kwani inaonyesha suluhisho bora.

Jukumu la 1.3

Onyesha orodha ya vitambulisho vya idara, idadi ya wafanyikazi ambayo haizidi watu 3.

Kimahusiano

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

kazi

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


Jukumu la 1.4

Onyesha orodha ya wafanyikazi ambao hawana meneja aliyeteuliwa anayefanya kazi katika idara moja.

Kimahusiano

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

kazi

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


Jukumu la 1.5

Pata orodha ya vitambulisho vya idara vilivyo na kiwango cha juu cha juu cha mshahara wa mfanyakazi.

Kimahusiano

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 )

kazi

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


Wacha tuendelee kwenye kazi ngumu zaidi kutoka kwa nyingine nakala. Ina uchambuzi wa kina wa jinsi ya kutekeleza kazi hii katika MS SQL.

Jukumu la 2.1

Ni wauzaji gani waliuza zaidi ya vipande 1997 vya bidhaa Nambari 30 mwaka 1?

Mantiki ya kikoa (kama hapo awali kwenye RDBMS tunaruka tamko):

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


Kimahusiano

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

kazi

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;


Jukumu la 2.2

Kwa kila mnunuzi (jina, jina), pata bidhaa mbili (jina) ambazo mnunuzi alitumia pesa nyingi zaidi mnamo 1997.

Tunapanua mantiki ya kikoa kutoka kwa mfano uliopita:

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

customer = DATA Customer (Order);

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


Kimahusiano

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

kazi

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;


Opereta ya PARTITION hufanya kazi kwa kanuni ifuatayo: ni muhtasari wa usemi uliobainishwa baada ya SUM (hapa 1), ndani ya vikundi vilivyobainishwa (hapa Mteja na Mwaka, lakini inaweza kuwa usemi wowote), ikipanga ndani ya vikundi kwa misemo iliyobainishwa katika ORDER ( hapa kununuliwa, na ikiwa ni sawa, basi kulingana na msimbo wa bidhaa wa ndani).

Jukumu la 2.3

Ni bidhaa ngapi zinahitaji kuagizwa kutoka kwa wasambazaji ili kutimiza maagizo ya sasa.

Wacha tupanue tena mantiki ya kikoa:

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

supplier = DATA Supplier (Product);

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


Kimahusiano

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

kazi

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;


Tatizo la nyota

Na mfano wa mwisho ni kutoka kwangu binafsi. Kuna mantiki ya mtandao wa kijamii. Watu wanaweza kuwa marafiki na kama kila mmoja. Kwa mtazamo wa hifadhidata inayofanya kazi ingeonekana kama hii:

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


Inahitajika kupata wagombea wanaowezekana kwa urafiki. Rasmi zaidi, unahitaji kupata watu wote A, B, C kama vile A ni marafiki na B, na B ni marafiki na C, A anapenda C, lakini A si marafiki na C.
Kwa mtazamo wa hifadhidata inayofanya kazi, swala ingeonekana kama hii:

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


Msomaji anahimizwa kutatua tatizo hili katika SQL peke yake. Inachukuliwa kuwa kuna marafiki wachache sana kuliko watu unaowapenda. Kwa hivyo ziko kwenye meza tofauti. Ikiwa imefanikiwa, pia kuna kazi yenye nyota mbili. Ndani yake, urafiki sio ulinganifu. Kwenye hifadhidata inayofanya kazi ingeonekana kama hii:

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: suluhisho la tatizo na nyota ya kwanza na ya pili kutoka 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 

Hitimisho

Ikumbukwe kwamba sintaksia ya lugha iliyotolewa ni chaguo mojawapo tu la kutekeleza dhana husika. SQL ilichukuliwa kama msingi, na lengo lilikuwa ni kufanana nayo iwezekanavyo. Bila shaka, wengine hawawezi kupenda majina ya maneno, rejista za maneno, nk. Jambo kuu hapa ni dhana yenyewe. Ikiwa inataka, unaweza kufanya syntax ya C++ na Python sawa.

Wazo la hifadhidata iliyoelezewa, kwa maoni yangu, ina faida zifuatazo:

• Unyenyekevu. Hiki ni kiashiria cha hali ya juu ambacho sio dhahiri katika hali rahisi. Lakini ikiwa unatazama kesi ngumu zaidi (kwa mfano, matatizo na nyota), basi, kwa maoni yangu, kuandika maswali hayo ni rahisi zaidi.
• Инкапсуляция. Katika baadhi ya mifano nilitangaza kazi za kati (kwa mfano, kuuzwa, kununuliwa nk), ambayo kazi zilizofuata zilijengwa. Hii inakuwezesha kubadilisha mantiki ya kazi fulani, ikiwa ni lazima, bila kubadilisha mantiki ya wale wanaowategemea. Kwa mfano, unaweza kufanya mauzo kuuzwa zilihesabiwa kutoka kwa vitu tofauti kabisa, wakati mantiki iliyobaki haitabadilika. Ndiyo, hii inaweza kutekelezwa katika RDBMS kwa kutumia CREATE VIEW. Lakini ikiwa mantiki yote imeandikwa kwa njia hii, haitaonekana kusoma sana.
• Hakuna pengo la kisemantiki. Hifadhidata kama hiyo inafanya kazi kwa kazi na madarasa (badala ya meza na uwanja). Kama tu katika programu ya kitamaduni (ikiwa tunadhania kuwa njia ni kazi na parameta ya kwanza katika mfumo wa darasa ambalo ni lake). Ipasavyo, inapaswa kuwa rahisi zaidi "kufanya urafiki" na lugha za programu za ulimwengu wote. Zaidi ya hayo, dhana hii inaruhusu utendakazi mgumu zaidi kutekelezwa. Kwa mfano, unaweza kupachika waendeshaji kama:

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


• Urithi na polymorphism. Katika hifadhidata inayofanya kazi, unaweza kutambulisha urithi mwingi kupitia CLASS ClassP: Class1, Class2 huunda na kutekeleza polima nyingi. Pengine nitaandika jinsi hasa katika makala zijazo.

Ingawa hii ni dhana tu, tayari tunayo utekelezaji fulani katika Java ambao hutafsiri mantiki yote ya utendaji kuwa mantiki ya uhusiano. Pamoja, mantiki ya uwakilishi na mambo mengine mengi yameunganishwa kwa uzuri, shukrani ambayo tunapata nzima. jukwaa. Kimsingi, tunatumia RDBMS (PostgreSQL pekee kwa sasa) kama "mashine pepe". Matatizo wakati mwingine huibuka na tafsiri hii kwa sababu kiboreshaji hoja cha RDBMS hakijui takwimu fulani ambazo FDBMS inazijua. Kwa nadharia, inawezekana kutekeleza mfumo wa usimamizi wa hifadhidata ambao utatumia muundo fulani kama uhifadhi, uliorekebishwa mahsusi kwa mantiki ya utendaji.

Chanzo: mapenzi.com