ProHoster > Блог > Gudanarwa > DBMS mai aiki

DBMS mai aiki

Duniyar bayanan bayanai ta dade tana mamaye DBMSs na alaƙa, waɗanda ke amfani da yaren SQL. Don haka ana kiran bambance-bambancen da ke tasowa NoSQL. Sun yi nasarar sassaƙa wani wuri don kansu a cikin wannan kasuwa, amma DBMSs na dangantaka ba za su mutu ba, kuma ana ci gaba da amfani da su sosai don manufofinsu.

A cikin wannan labarin ina so in bayyana manufar rumbun adana bayanai mai aiki. Don ingantacciyar fahimta, zan yi wannan ta hanyar kwatanta shi da ƙirar alaƙa ta gargajiya. Matsaloli daga gwaje-gwajen SQL daban-daban da aka samu akan Intanet za a yi amfani da su azaman misalai.

Gabatarwar

Rukunin bayanai masu alaƙa suna aiki akan teburi da filayen. A cikin bayanan aiki, za a yi amfani da azuzuwan da ayyuka a maimakon haka, bi da bi. Filin da ke cikin tebur mai maɓallan N za a wakilta shi azaman aikin sigogin N. Maimakon dangantaka tsakanin tebur, za a yi amfani da ayyuka waɗanda ke mayar da abubuwan ajin da aka haɗa haɗin zuwa. Za a yi amfani da abun da ke cikin aiki maimakon JOIN.

Kafin matsawa kai tsaye zuwa ayyuka, zan bayyana aikin dabaru na yanki. Don DDL zan yi amfani da haɗin gwiwar PostgreSQL. Don aiki yana da nasa tsarin aiki.

Tables da filayen

Abu mai sauƙi na Sku mai suna da filayen farashi:

Dangantaka

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

m

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

Mun sanar da biyu ayyuka, wanda ke ɗaukar siga guda ɗaya Sku azaman shigarwa kuma dawo da nau'in farko.

Ana ɗauka cewa a cikin DBMS mai aiki kowane abu zai sami wasu lambobi na ciki wanda aka ƙirƙira ta atomatik kuma ana iya isa gare shi idan ya cancanta.

Bari mu saita farashin samfur / kantin sayar da / mai kaya. Yana iya canzawa akan lokaci, don haka bari mu ƙara filin lokaci a teburin. Zan tsallake ayyana teburi don kundayen adireshi a cikin bayanan da ke da alaƙa don taƙaita lambar:

Dangantaka

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

m

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

Fihirisa

Misali na ƙarshe, za mu gina fihirisa akan duk maɓalli da kwanan wata domin mu sami saurin samun farashi na takamaiman lokaci.

Dangantaka

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

m

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

ayyuka

Bari mu fara da ƙananan matsalolin da aka ɗauka daga daidaitattun labarai da Habr.

Da farko, bari mu bayyana dabarun yanki (don bayanan alaƙa ana yin hakan kai tsaye a cikin labarin da ke sama).

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

Aiki 1.1

Nuna jerin sunayen ma'aikatan da suka karɓi albashi fiye da na mai kula da su.

Dangantaka

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

m

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

Aiki 1.2

Yi lissafin ma'aikatan da suka karɓi mafi girman albashi a sashinsu

Dangantaka

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

m

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

Duka aiwatarwa daidai suke. A yanayin farko, a cikin ma'ajin bayanai na dangantaka za ku iya amfani da CREATE VIEW, wanda hakanan zai fara lissafin matsakaicin albashin wani sashe na musamman a cikinsa. A cikin abin da ke biyo baya, don tsabta, zan yi amfani da shari'ar farko, tun da yake mafi kyau yana nuna mafita.

Aiki 1.3

Nuna jerin ID na sashen, adadin ma'aikatan da bai wuce mutane 3 ba.

Dangantaka

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

m

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

Aiki 1.4

Nuna jerin ma'aikatan da ba su da naɗaɗɗen manajan da ke aiki a sashe ɗaya.

Dangantaka

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

m

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

Aiki 1.5

Nemo jerin ID na sashen tare da matsakaicin adadin albashin ma'aikata.

Dangantaka

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 )

m

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

Bari mu matsa zuwa ƙarin ayyuka masu rikitarwa daga wani labarai. Ya ƙunshi cikakken bincike na yadda ake aiwatar da wannan aikin a cikin MS SQL.

Aiki 2.1

Wadanne masu sayarwa ne suka sayar da fiye da raka'a 1997 na samfur No. 30 a 1?

Domain Logic (kamar a baya akan RDBMS mun tsallake sanarwar):

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

Dangantaka

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

m

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;

Aiki 2.2

Ga kowane mai siye (suna, sunan mahaifi), nemo kaya biyu (suna) wanda mai siye ya kashe mafi yawan kuɗi a cikin 1997.

Mun tsawaita ma'anar yanki daga misalin da ya gabata:

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

customer = DATA Customer (Order);

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

Dangantaka

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

m

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;

Ma'aikacin PARTITION yana aiki akan ka'ida mai zuwa: yana taƙaita bayanin da aka ƙayyade bayan SUM (a nan 1), a cikin ƙayyadaddun ƙungiyoyi (a nan Abokin ciniki da Shekara, amma zai iya zama kowane magana), rarraba cikin ƙungiyoyi ta hanyar maganganun da aka ƙayyade a cikin ORDER ( Anan saya, kuma idan daidai, to bisa ga lambar samfurin ciki).

Aiki 2.3

Kayayya nawa ne ake buƙatar oda daga masu ba da kaya don cika umarni na yanzu.

Bari mu sake fadada dabaru na yanki:

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

supplier = DATA Supplier (Product);

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

Dangantaka

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

m

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;

Matsala tare da alamar alama

Kuma misali na ƙarshe daga gare ni ne. Akwai dabaru na hanyar sadarwar zamantakewa. Mutane za su iya zama abokai da juna kuma suna son juna. Daga mahangar bayanai mai aiki zai yi kama da haka:

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

Wajibi ne a nemo masu yuwuwar abokantaka. A bisa ƙa'ida, kuna buƙatar nemo duk mutane A, B, C waɗanda A ke abokantaka ne da B, B kuma abokane ne da C, A likes C, amma A ba abokai bane da C.
Daga mahangar bayanan aiki, tambayar zata yi kama da haka:

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

Ana son mai karatu ya warware wannan matsala a cikin SQL da kansa. Ana ɗauka cewa akwai ƙarancin abokai fiye da mutanen da kuke so. Don haka suna cikin tebur daban. Idan nasara, akwai kuma aiki tare da taurari biyu. A ciki, abota ba ta da ma'ana. A kan ma'auni mai aiki zai yi kama da haka:

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: maganin matsalar tare da alamar farko da ta biyu daga 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

ƙarshe

Ya kamata a lura cewa ƙayyadaddun kalmomin da aka ba da harshe ɗaya ne kawai daga cikin zaɓuɓɓukan aiwatar da manufar da aka bayar. An dauki SQL a matsayin tushe, kuma manufar ita ce ta kasance daidai da shi. Tabbas, wasu ƙila ba sa son sunayen kalmomi, rajistar kalmomi, da sauransu. Babban abu a nan shi ne manufar kanta. Idan ana so, zaku iya yin duka C++ da Python iri ɗaya.

Tunanin da aka bayyana, a ganina, yana da fa'idodi masu zuwa:

  • 'yanci. Wannan alama ce ta zahiri wacce ba ta bayyana ba a lokuta masu sauki. Amma idan kun kalli wasu lokuta masu rikitarwa (misali, matsaloli tare da asterisks), to, a ganina, rubuta irin waɗannan tambayoyin ya fi sauƙi.
  • Yankin. A wasu misalan na ayyana ayyukan tsaka-tsaki (misali, sayar, sayi da dai sauransu), daga abin da aka gina ayyuka na gaba. Wannan yana ba ku damar canza tunanin wasu ayyuka, idan ya cancanta, ba tare da canza tunanin waɗanda suka dogara da su ba. Misali, zaku iya yin tallace-tallace sayar an ƙididdige su daga abubuwa daban-daban, yayin da sauran dabaru ba za su canza ba. Ee, ana iya aiwatar da wannan a cikin RDBMS ta amfani da CREATE VIEW. Amma idan an rubuta duk dabaru ta wannan hanya, ba zai yi kama da abin karantawa ba.
  • Babu tazarar ma'ana. Irin wannan bayanan yana aiki akan ayyuka da azuzuwan (maimakon tebur da filayen). Kamar dai a cikin shirye-shiryen gargajiya (idan muka ɗauka cewa hanya tana aiki ne tare da ma'aunin farko a cikin nau'in ajin da ya dace). Saboda haka, ya kamata ya zama mafi sauƙi don "abokai" tare da harsunan shirye-shiryen duniya. Bugu da ƙari, wannan ra'ayi yana ba da damar aiwatar da ayyuka masu rikitarwa da yawa. Misali, zaku iya shigar da masu aiki kamar:

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

  • Gado da polymorphism. A cikin bayanan aiki, zaku iya gabatar da gado mai yawa ta hanyar CLASS ClassP: Class1, Class2 yana ginawa da aiwatar da polymorphism da yawa. Wataƙila zan rubuta yadda daidai a cikin labarai na gaba.

Ko da yake wannan ra'ayi ne kawai, mun riga mun sami wasu aiwatarwa a cikin Java wanda ke fassara duk dabaru na aiki zuwa dabaru na alaƙa. Bugu da ƙari, dabaru na wakilci da sauran abubuwa da yawa suna da kyau a haɗe da shi, godiya ga abin da muke samun duka. dandamali. Ainihin, muna amfani da RDBMS (PostgreSQL kawai a yanzu) azaman "na'ura mai kama-da-wane". Matsaloli a wasu lokuta suna tasowa tare da wannan fassarar saboda mai inganta tambayar RDBMS bai san wasu ƙididdiga waɗanda FDBMS suka sani ba. A ka'idar, yana yiwuwa a aiwatar da tsarin sarrafa bayanai wanda zai yi amfani da wani tsari azaman ajiya, wanda aka daidaita musamman don dabarun aiki.

source: www.habr.com

Add a comment