ProHoster > blog > Gweinyddiaeth > DBMS swyddogaethol

DBMS swyddogaethol

Mae byd y gronfa ddata wedi'i feddiannu ers tro gan DBMSs perthynol sy'n defnyddio'r iaith SQL. Cymaint felly fel bod mathau sy'n dod i'r amlwg yn cael eu galw'n NoSQL. Llwyddasant i ennill lle penodol iddynt eu hunain yn y farchnad hon, ond nid yw DBMS perthynol yn mynd i farw, ac maent yn parhau i gael eu defnyddio'n weithredol at eu dibenion eu hunain.

Yn yr erthygl hon, rwyf am ddisgrifio'r cysyniad o gronfa ddata swyddogaethol. I gael gwell dealltwriaeth, byddaf yn gwneud hyn trwy gymharu â'r model perthynol clasurol. Fel enghreifftiau, bydd tasgau o wahanol brofion SQL a geir ar y Rhyngrwyd yn cael eu defnyddio.

Cyflwyniad

Mae cronfeydd data perthynol yn gweithredu ar dablau a meysydd. Mewn cronfa ddata swyddogaethol, bydd dosbarthiadau a swyddogaethau yn cael eu defnyddio yn lle hynny, yn y drefn honno. Bydd maes mewn tabl gyda bysellau N yn cael ei gynrychioli fel swyddogaeth paramedrau N. Yn lle dolenni rhwng tablau, bydd ffwythiannau'n cael eu defnyddio sy'n dychwelyd gwrthrychau'r dosbarth y mae'r ddolen yn mynd iddo. Bydd cyfansoddiad swyddogaeth yn cael ei ddefnyddio yn lle JOIN.

Cyn symud ymlaen yn uniongyrchol at y tasgau, byddaf yn disgrifio'r dasg o resymeg parth. Ar gyfer DDL, byddaf yn defnyddio cystrawen PostgreSQL. Ar gyfer swyddogaethol ei hun cystrawen.

Byrddau a chaeau

Gwrthrych Sku syml gyda meysydd enw a phris:

perthynol

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

swyddogaethol

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

Rydym yn cyhoeddi dau swyddogaethau, sy'n cymryd un paramedr Sku fel mewnbwn a dychwelyd math cyntefig.

Mewn DBMS swyddogaethol, tybir y bydd gan bob gwrthrych rywfaint o god mewnol a gynhyrchir yn awtomatig ac y gellir ei gyrchu os oes angen.

Gadewch i ni osod y pris ar gyfer y cynnyrch / siop / cyflenwr. Gall newid dros amser, felly gadewch i ni ychwanegu maes amser at y bwrdd. Byddaf yn hepgor y datganiad o dablau ar gyfer cyfeiriaduron mewn cronfa ddata berthynol i fyrhau'r cod:

perthynol

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

swyddogaethol

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

Mynegeion

Ar gyfer yr enghraifft olaf, gadewch i ni adeiladu mynegai ar yr holl allweddi a dyddiad fel y gallwn ddod o hyd i'r pris yn gyflym am amser penodol.

perthynol

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

swyddogaethol

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

tasgau

Gadewch i ni ddechrau gyda phroblemau cymharol syml a gymerwyd o'r cyfatebol erthyglau ar Habr.

Yn gyntaf, gadewch i ni ddatgan rhesymeg y parth (ar gyfer cronfa ddata berthynol, gwneir hyn yn uniongyrchol yn yr erthygl uchod).

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

Tasg 1.1

Dangoswch restr o weithwyr sy'n derbyn cyflogau uwch na rhai'r goruchwyliwr uniongyrchol.

perthynol

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

swyddogaethol

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

Tasg 1.2

Arddangos rhestr o weithwyr sy'n ennill y cyflog uchaf yn eu hadran

perthynol

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

swyddogaethol

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

Mae'r ddau weithrediad yn gyfwerth. Ar gyfer yr achos cyntaf yn y gronfa ddata berthynol, gallwch ddefnyddio CREATE VIEW, a fydd yn yr un modd yn cyfrifo'r uchafswm cyflog ar gyfer adran benodol ynddi yn gyntaf. Yn y dyfodol, er eglurder, byddaf yn defnyddio'r achos cyntaf, gan ei fod yn adlewyrchu'r ateb yn well.

Tasg 1.3

Arddangos rhestr o IDs adran, nifer y gweithwyr nad yw'n fwy na 3 o bobl.

perthynol

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

swyddogaethol

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

Tasg 1.4

Arddangos rhestr o weithwyr nad oes ganddynt reolwr penodedig yn gweithio yn yr un adran.

perthynol

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

swyddogaethol

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

Tasg 1.5

Dewch o hyd i'r rhestr o IDau adrannau gydag uchafswm cyfanswm cyflog gweithwyr.

perthynol

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 )

swyddogaethol

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

Gadewch i ni symud ymlaen at dasgau mwy cymhleth o un arall erthyglau. Mae'n cynnwys dadansoddiad manwl o sut i weithredu'r dasg hon yn MS SQL.

Tasg 2.1

Pa werthwyr a werthodd fwy na 1997 darn o eitem #30 ym 1?

Rhesymeg parth (fel o'r blaen, rydym yn hepgor y datganiad ar yr RDBMS):

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

perthynol

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

swyddogaethol

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;

Tasg 2.2

Ar gyfer pob cwsmer (enw cyntaf, enw olaf), darganfyddwch y ddwy eitem (enw) y gwariodd y cwsmer fwyaf o arian arnynt yn 1997.

Ymestyn rhesymeg y parth o'r enghraifft flaenorol:

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

customer = DATA Customer (Order);

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

perthynol

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

swyddogaethol

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;

Mae'r gweithredwr RHANNU yn gweithio yn unol â'r egwyddor ganlynol: mae'n crynhoi'r ymadrodd a nodir ar ôl SUM (yma 1) o fewn y grwpiau penodedig (yma Cwsmer a Blwyddyn, ond gall fod yn unrhyw fynegiant), gan ddidoli o fewn y grwpiau yn ôl yr ymadroddion a nodir yn GORCHYMYN ( yma wedi'i brynu, ac os ydyn nhw'n gyfartal, yna yn ôl y cod cynnyrch mewnol).

Tasg 2.3

Faint o nwyddau sydd angen eu harchebu gan gyflenwyr i gyflawni archebion cyfredol.

Gadewch i ni ymestyn rhesymeg y parth eto:

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

supplier = DATA Supplier (Product);

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

perthynol

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

swyddogaethol

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;

Tasg gyda seren

Ac mae'r enghraifft olaf oddi wrthyf yn bersonol. Mae yna resymeg rhwydwaith cymdeithasol. Gall pobl fod yn ffrindiau â'i gilydd ac yn hoffi ei gilydd. O safbwynt cronfa ddata swyddogaethol, byddai hyn yn edrych fel hyn:

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

Mae angen dod o hyd i ymgeiswyr posibl ar gyfer cyfeillgarwch. Yn fwy ffurfiol, mae angen ichi ddod o hyd i'r holl bobl A, B, C fel bod A yn ffrindiau â B, a B yn ffrindiau â C, mae A yn hoffi C, ond nid yw A yn ffrindiau â C.
O safbwynt cronfa ddata swyddogaethol, byddai'r ymholiad yn edrych fel hyn:

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

Gwahoddir y darllenydd i ddatrys y broblem hon yn annibynnol yn SQL. Tybir fod llawer llai o gyfeillion na'r rhai sydd yn hoffi. Felly, maent mewn tablau ar wahân. Yn achos datrysiad llwyddiannus, mae problem gyda dwy seren hefyd. Nid yw ei chyfeillgarwch yn gymesur. Ar gronfa ddata swyddogaethol byddai'n edrych fel hyn:

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: datrys y broblem gyda'r seren gyntaf a'r ail seren dss_calika:

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

Casgliad

Dylid nodi mai dim ond un o'r opsiynau ar gyfer gweithredu'r cysyniad uchod yw'r gystrawen uchod o'r iaith. SQL a gymerwyd fel sail, a'r nod oedd ei wneud mor debyg â phosibl iddo. Wrth gwrs, efallai na fydd rhywun yn hoffi enwau geiriau allweddol, achos geiriau, ac ati. Y prif beth yma yw'r cysyniad ei hun. Os dymunir, gallwch wneud cystrawen C ++ a Python yn debyg.

Mae gan y cysyniad cronfa ddata a ddisgrifir, yn fy marn i, y manteision canlynol:

  • rhwyddineb. Mae hwn yn ddangosydd cymharol oddrychol nad yw'n amlwg mewn achosion syml. Ond os edrychwch ar achosion mwy cymhleth (er enghraifft, tasgau gyda sêr), yna, yn fy marn i, mae ysgrifennu ymholiadau o'r fath yn llawer haws.
  • Инкапсуляция. Mewn rhai enghreifftiau, datganais swyddogaethau canolradd (er enghraifft, gwerthu, prynu ac ati), y cafodd swyddogaethau dilynol eu hadeiladu ohono. Mae hyn yn caniatáu ichi newid rhesymeg rhai swyddogaethau, os oes angen, heb newid rhesymeg y rhai sy'n dibynnu arnynt. Er enghraifft, gallwch chi wneud gwerthiant gwerthu eu cyfrifo o wrthrychau hollol wahanol, tra na fydd gweddill y rhesymeg yn newid. Oes, yn RDBMS gellir gwneud hyn gyda CREATE VIEW. Ond os ydych chi'n ysgrifennu'r holl resymeg yn y modd hwn, yna ni fydd yn edrych yn ddarllenadwy iawn.
  • Dim Bwlch Semantig. Mae cronfa ddata o'r fath yn gweithredu gyda swyddogaethau a dosbarthiadau (yn hytrach na thablau a meysydd). Yn yr un modd ag mewn rhaglennu clasurol (gan dybio bod dull yn swyddogaeth gyda'r paramedr cyntaf ar ffurf dosbarth y mae'n perthyn iddo). Yn unol â hynny, dylai fod yn llawer haws “gwneud ffrindiau” ag ieithoedd rhaglennu cyffredinol. Yn ogystal, mae'r cysyniad hwn yn caniatáu ichi weithredu swyddogaethau llawer mwy cymhleth. Er enghraifft, gallwch chi fewnosod datganiadau fel hyn yn y gronfa ddata:

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

  • Etifeddiaeth a polymorphism. Mewn cronfa ddata swyddogaethol, gallwch chi gyflwyno etifeddiaeth luosog trwy'r CLASS ClassP: Class1, Class2 yn adeiladu a gweithredu amryffurfedd lluosog. Sut yn union, efallai y byddaf yn ysgrifennu yn yr erthyglau canlynol.

Er mai cysyniad yn unig yw hwn, mae gennym eisoes rywfaint o weithredu yn Java sy'n trosi'r holl resymeg swyddogaethol yn rhesymeg berthynol. Hefyd, mae rhesymeg cynrychioliadau a llawer o bethau eraill wedi'u sgriwio'n hyfryd iddo, ac rydym yn cael y cyfan diolch i hynny. platfform. Yn y bôn, rydym yn defnyddio RDBMS (dim ond PostgreSQL hyd yn hyn) fel "peiriant rhithwir". Mae'r cyfieithiad hwn weithiau'n achosi problemau oherwydd nad yw optimizer ymholiad RDBMS yn gwybod ystadegau penodol y mae'r FDBMS yn eu gwybod. Mewn theori, mae'n bosibl gweithredu system rheoli cronfa ddata a fydd yn defnyddio strwythur penodol fel storfa, wedi'i addasu'n benodol ar gyfer rhesymeg swyddogaethol.

Ffynhonnell: hab.com

Ychwanegu sylw