Heimur gagnagrunna hefur lengi verið einkennist af vensla DBMS, sem nota SQL tungumálið. Svo mikið að ný afbrigði eru kölluð NoSQL. Þeim tókst að móta sér ákveðinn sess á þessum markaði, en venslabundin DBMS eru ekki að fara að deyja og halda áfram að vera virkan notuð í tilgangi þeirra.
Í þessari grein vil ég lýsa hugmyndinni um hagnýtan gagnagrunn. Til að fá betri skilning mun ég gera þetta með því að bera það saman við klassíska venslalíkanið. Vandamál úr ýmsum SQL prófum sem finnast á netinu verða notuð sem dæmi.
Inngangur
Venslagagnagrunnar starfa á töflum og sviðum. Í virkum gagnagrunni verða flokkar og aðgerðir notaðar í staðinn, hvort um sig. Reitur í töflu með N lyklum verður sýndur sem fall af N breytum. Í stað tengsla á milli taflna verða notuð föll sem skila hlutum í þeim flokki sem tengingin er við. Aðgerðasamsetning verður notuð í stað JOIN.
Áður en ég fer beint yfir í verkefnin mun ég lýsa verkefni lénsrökfræði. Fyrir DDL mun ég nota PostgreSQL setningafræði. Fyrir hagnýtur hefur það sína eigin setningafræði.
Töflur og reiti
Einfaldur Sku hlutur með nafn- og verðreitum:
Vensla
CREATE TABLE Sku
(
id bigint NOT NULL,
name character varying(100),
price numeric(10,5),
CONSTRAINT id_pkey PRIMARY KEY (id)
)
Hagnýtur
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
Við tilkynnum tvo aðgerðir, sem taka eina færibreytu Sku sem inntak og skila frumstæðri gerð.
Gert er ráð fyrir að í virku DBMS hafi hver hlutur einhvern innri kóða sem er sjálfkrafa búinn til og hægt er að nálgast hann ef þörf krefur.
Setjum verðið fyrir vöruna/verslunina/birgjann. Það gæti breyst með tímanum, svo við skulum bæta tímareit við töfluna. Ég mun sleppa því að lýsa yfir töflum fyrir möppur í venslagagnagrunni til að stytta kóðann:
Vensla
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)
)
Hagnýtur
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
Vísitölur
Fyrir síðasta dæmið munum við byggja vísitölu á alla lykla og dagsetninguna svo að við getum fljótt fundið verðið fyrir ákveðinn tíma.
Vensla
CREATE INDEX prices_date
ON prices
(skuId, storeId, supplierId, dateTime)
Hagnýtur
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
verkefni
Við skulum byrja á tiltölulega einföldum vandamálum sem tekin eru úr samsvarandi á Habr.
Í fyrsta lagi skulum við lýsa yfir lénsrökfræðinni (fyrir tengslagagnagrunninn er þetta gert beint í greininni hér að ofan).
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);
Verkefni 1.1
Birta lista yfir starfsmenn sem fá hærri laun en næsta yfirmanns.
Vensla
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
Hagnýtur
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
Verkefni 1.2
Skráðu þá starfsmenn sem fá hámarkslaun í sinni deild
Vensla
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )
Hagnýtur
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));
Báðar útfærslurnar eru jafngildar. Í fyrra tilvikinu, í venslagagnagrunni er hægt að nota CREATE VIEW, sem á sama hátt mun fyrst reikna út hámarkslaun fyrir tiltekna deild í honum. Í því sem á eftir fer, til glöggvunar, mun ég nota fyrra tilvikið, þar sem það endurspeglar lausnina betur.
Verkefni 1.3
Birta lista yfir auðkenni deilda, fjöldi starfsmanna sem fer ekki yfir 3 manns.
Vensla
select department_id
from employee
group by department_id
having count(*) <= 3
Hagnýtur
countEmployees 'Количество сотрудников' (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;
Verkefni 1.4
Birta lista yfir starfsmenn sem ekki hafa tilnefndan yfirmann sem starfar í sömu deild.
Vensla
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
Hagnýtur
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
Verkefni 1.5
Finndu lista yfir auðkenni deildar með hámarks heildarlaunum starfsmanna.
Vensla
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 )
Hagnýtur
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();
Förum yfir í flóknari verkefni frá öðru . Það inniheldur ítarlega greiningu á því hvernig á að útfæra þetta verkefni í MS SQL.
Verkefni 2.1
Hvaða seljendur seldu meira en 1997 einingar af vöru nr. 30 árið 1?
Lénsrökfræði (eins og áður á RDBMS sleppum við yfirlýsingunni):
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);
Vensla
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
Hagnýtur
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;
Verkefni 2.2
Fyrir hvern kaupanda (nafn, eftirnafn), finndu þær tvær vörur (nafn) sem kaupandinn eyddi mestum peningum í árið 1997.
Við framlengjum lénsrökfræðina frá fyrra dæmi:
CLASS Customer 'Клиент';
contactName 'ФИО' = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
Vensla
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
Hagnýtur
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 rekstraraðilinn vinnur eftir eftirfarandi meginreglu: hann leggur saman tjáninguna sem tilgreind er á eftir SUM (hér 1), innan tilgreindra hópa (hér Viðskiptavinur og Ár, en gæti verið hvaða tjáning sem er), flokkar innan hópanna eftir tjáningunum sem tilgreind eru í ORDER ( hér keypt, og ef jafnt, þá samkvæmt innri vörukóða).
Verkefni 2.3
Hversu margar vörur þarf að panta frá birgjum til að uppfylla núverandi pantanir.
Við skulum víkka út lénslógíkina aftur:
CLASS Supplier 'Поставщик';
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock 'Остаток на складе' = DATA NUMERIC[10,3] (Product);
reorderLevel 'Норма продажи' = DATA NUMERIC[10,3] (Product);
Vensla
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
Hagnýtur
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;
Vandamál með stjörnu
Og síðasta dæmið er frá mér persónulega. Það er rökfræði félagslegs nets. Fólk getur verið vinir hvert við annað og líkað við hvert annað. Frá sjónarhóli hagnýtra gagnagrunns myndi það líta svona út:
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
Nauðsynlegt er að finna mögulega frambjóðendur fyrir vináttu. Meira formlega, þú þarft að finna alla A, B, C þannig að A er vinur B og B er vinur C, A líkar við C, en A er ekki vinur C.
Frá sjónarhóli hagnýtra gagnagrunns myndi fyrirspurnin líta svona út:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
friends(a, b) AND friends(b, c);
Lesandinn er hvattur til að leysa þetta vandamál í SQL upp á eigin spýtur. Það er gert ráð fyrir að það séu miklu færri vinir en fólk sem þér líkar við. Þess vegna eru þær í aðskildum töflum. Ef vel tekst til er líka verkefni með tveimur stjörnum. Í henni er vinátta ekki samhverf. Á virkum gagnagrunni myndi það líta svona út:
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: lausn á vandamálinu með fyrstu og annarri stjörnu frá :
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
Ályktun
Það skal tekið fram að tiltekin málsetningafræði er aðeins einn af valkostunum til að útfæra tiltekið hugtak. SQL var lagt til grundvallar og markmiðið var að það yrði sem líkt því. Auðvitað getur verið að sumum líkar ekki við nöfn á leitarorðum, orðaskrám o.s.frv. Aðalatriðið hér er hugmyndin sjálf. Ef þess er óskað geturðu gert bæði C++ og Python svipaða setningafræði.
Lýst gagnagrunnshugtakið hefur að mínu mati eftirfarandi kosti:
- vellíðan. Þetta er tiltölulega huglægur vísir sem er ekki augljós í einföldum tilfellum. En ef þú horfir á flóknari tilvik (til dæmis vandamál með stjörnum), þá er að mínu mati miklu auðveldara að skrifa slíkar fyrirspurnir.
- Инкапсуляция. Í sumum dæmum lýsti ég yfir milliföllum (td. selt, keypti o.fl.), sem síðari aðgerðir voru byggðar upp úr. Þetta gerir þér kleift að breyta rökfræði ákveðinna aðgerða, ef nauðsyn krefur, án þess að breyta rökfræði þeirra sem eru háðar þeim. Til dæmis er hægt að selja selt voru reiknuð út frá gjörólíkum hlutum, en restin af rökfræðinni mun ekki breytast. Já, þetta er hægt að útfæra í RDBMS með því að nota CREATE VIEW. En ef öll rökfræðin er skrifuð á þennan hátt mun hún ekki líta mjög læsileg út.
- Ekkert merkingarlegt bil. Slíkur gagnagrunnur starfar á föllum og flokkum (í stað töflur og reita). Rétt eins og í klassískri forritun (ef við gefum okkur að aðferð sé fall með fyrstu breytu í formi flokks sem hún tilheyrir). Í samræmi við það ætti að vera miklu auðveldara að „eignast vini“ með alhliða forritunarmálum. Að auki gerir þetta hugtak kleift að innleiða mun flóknari virkni. Til dæmis geturðu fellt inn rekstraraðila eins og:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'Петя' MESSAGE 'Что-то Петя продает слишком много одного товара в 2019 году';
- Erfðir og fjölbreytni. Í virkum gagnagrunni er hægt að kynna margfalda arfleifð í gegnum CLASS ClassP: Class1, Class2 smíðar og innleiða margbreytileika. Ég mun líklega skrifa hvernig nákvæmlega í næstu greinum.
Jafnvel þó að þetta sé bara hugtak, höfum við nú þegar einhverja útfærslu í Java sem þýðir alla hagnýta rökfræði yfir í venslarökfræði. Auk þess er rökfræði framsetninga og margt annað fallega fest við hana, þökk sé henni fáum við heild . Í meginatriðum notum við RDBMS (aðeins PostgreSQL í bili) sem „sýndarvél“. Vandamál koma stundum upp við þessa þýðingu vegna þess að RDBMS fyrirspurnarfínstillingin þekkir ekki ákveðna tölfræði sem FDBMS þekkir. Fræðilega séð er hægt að innleiða gagnagrunnsstjórnunarkerfi sem mun nota ákveðna uppbyggingu sem geymslu, aðlagað sérstaklega fyrir starfræna rökfræði.
Heimild: www.habr.com