ProHoster > Блог > Захиргаа > Функциональ DBMS

Функциональ DBMS

Өгөгдлийн сангийн ертөнцөд удаан хугацааны туршид SQL хэлийг ашигладаг харилцааны DBMS-ууд ноёрхож ирсэн. Тиймээс шинээр гарч ирж буй хувилбаруудыг NoSQL гэж нэрлэдэг. Тэд энэ зах зээлд тодорхой байр сууриа олж чадсан боловч харилцааны DBMS нь үхэхгүй бөгөөд зорилгодоо идэвхтэй ашиглагдаж байна.

Энэ нийтлэлд би функциональ мэдээллийн сангийн тухай ойлголтыг тайлбарлахыг хүсч байна. Илүү сайн ойлгохын тулд би үүнийг сонгодог харилцааны загвартай харьцуулах замаар хийх болно. Интернетээс олдсон янз бүрийн SQL тестийн асуудлуудыг жишээ болгон ашиглах болно.

Танилцуулга

Харилцааны мэдээллийн сан нь хүснэгт болон талбарууд дээр ажилладаг. Функциональ мэдээллийн санд анги, функцийг тус тусад нь ашиглана. Хүснэгтийн N товчлуур бүхий талбарыг N параметрийн функцээр дүрсэлнэ. Хүснэгт хоорондын харилцааны оронд холболт хийгдсэн ангийн объектуудыг буцаах функцуудыг ашиглана. JOIN-ын оронд функцийн бүтцийг ашиглана.

Даалгаврууд руу шууд шилжихээсээ өмнө би домайн логикийн даалгаврыг тайлбарлах болно. DDL-ийн хувьд би PostgreSQL синтакс ашиглах болно. Функциональ хувьд энэ нь өөрийн гэсэн синтакстай байдаг.

Хүснэгт ба талбарууд

Нэр болон үнийн талбар бүхий энгийн Sku объект:

Харилцааны

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

Ашигтай

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

Бид хоёрыг зарлаж байна үйл ажиллагаа, Sku нэг параметрийг оролт болгон авч, анхдагч төрлийг буцаана.

Функциональ DBMS-д объект бүр автоматаар үүсгэгддэг дотоод кодтой байх ба шаардлагатай бол хандах боломжтой гэж үздэг.

Бүтээгдэхүүн/дэлгүүр/нийлүүлэгчийн үнийг тогтооё. Энэ нь цаг хугацааны явцад өөрчлөгдөж магадгүй тул хүснэгтэд цагийн талбар нэмье. Би кодыг богиносгохын тулд харилцааны мэдээллийн сан дахь лавлах хүснэгтүүдийг зарлахыг алгасах болно.

Харилцааны

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

Ашигтай

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

Индексүүд

Сүүлийн жишээний хувьд бид бүх түлхүүрүүд болон огноо дээр индексийг бүтээх бөгөөд ингэснээр тодорхой хугацааны үнийг хурдан олох боломжтой болно.

Харилцааны

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

Ашигтай

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

үүрэг

Харгалзах асуудлуудаас авсан харьцангуй энгийн асуудлуудаас эхэлье нийтлэл Хабр дээр.

Эхлээд домэйн логикийг зарлацгаая (харилцааны мэдээллийн сангийн хувьд энэ нь дээрх нийтлэлд шууд хийгдсэн).

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

Даалгавар 1.1

Шууд удирдагчийнхаа цалингаас илүү цалин авдаг ажилчдын жагсаалтыг харуул.

Харилцааны

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

Ашигтай

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

Даалгавар 1.2

Өөрийн хэлтэст хамгийн их цалин авдаг ажилчдыг жагсаа

Харилцааны

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

Ашигтай

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

Хоёр хэрэгжилт хоёулаа тэнцүү байна. Эхний тохиолдолд, харилцааны мэдээллийн санд та CREATE VIEW-ийг ашиглаж болох бөгөөд энэ нь эхлээд тодорхой хэлтсийн цалингийн дээд хэмжээг тооцоолох болно. Дараах зүйлд, тодорхой болгохын тулд би эхний тохиолдлыг ашиглах болно, учир нь энэ нь шийдлийг илүү сайн тусгасан болно.

Даалгавар 1.3

Ажилчдын тоо 3 хүнээс хэтрэхгүй хэлтсийн ID-ийн жагсаалтыг харуул.

Харилцааны

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

Ашигтай

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

Даалгавар 1.4

Нэг хэлтэст томилогдсон менежергүй ажилчдын жагсаалтыг харуулах.

Харилцааны

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

Ашигтай

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

Даалгавар 1.5

Ажилчдын нийт цалингийн дээд хэмжээ бүхий хэлтсийн үнэмлэхний жагсаалтыг олоорой.

Харилцааны

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 )

Ашигтай

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

Өөр илүү төвөгтэй даалгавар руу шилжье нийтлэл. Энэ нь MS SQL дээр энэ даалгаврыг хэрхэн хэрэгжүүлэх талаар нарийвчилсан дүн шинжилгээг агуулдаг.

Даалгавар 2.1

1997 онд ямар худалдагч 30 гаруй ширхэг No1 бүтээгдэхүүн борлуулсан бэ?

Домэйн логик (Өмнө нь 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);

Харилцааны

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

Ашигтай

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;

Даалгавар 2.2

Худалдан авагч бүрийн хувьд (нэр, овог) 1997 онд худалдан авагч хамгийн их мөнгө зарцуулсан хоёр барааг (нэр) ол.

Бид өмнөх жишээнээс домэйн логикийг өргөтгөсөн:

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

customer = DATA Customer (Order);

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

Харилцааны

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

Ашигтай

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 оператор нь дараах зарчмаар ажилладаг: SUM-ын дараа заасан илэрхийллийг (энд 1), заасан бүлгүүдийн дотор (энд Хэрэглэгч ба Жил, гэхдээ дурын илэрхийлэл байж болно) нийлбэрлэж, ЗАХИАЛГА-д заасан илэрхийллээр бүлгүүдэд эрэмбэлнэ. энд худалдаж авсан бөгөөд хэрэв тэнцүү бол дотоод бүтээгдэхүүний кодын дагуу).

Даалгавар 2.3

Одоогийн захиалгыг биелүүлэхийн тулд ханган нийлүүлэгчдээс хичнээн бараа захиалах шаардлагатай.

Домэйн логикийг дахин өргөжүүлье:

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

supplier = DATA Supplier (Product);

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

Харилцааны

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

Ашигтай

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;

Одтой холбоотой асуудал

Сүүлийн жишээ бол миний хувьд. Нийгмийн сүлжээний логик гэж байдаг. Хүмүүс хоорондоо найзалж, бие биедээ дуртай байж болно. Функциональ өгөгдлийн сангийн хэтийн төлөвөөс харахад дараах байдалтай байна.

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

Нөхөрлөлийн боломжит нэр дэвшигчдийг олох шаардлагатай байна. Илүү албан ёсоор бол та А, В, С бүх хүмүүсийг олох хэрэгтэй бөгөөд ингэснээр А нь В-тэй найзалж, Б нь С-тэй найзалж, А нь С-д дуртай, харин А нь С-тэй найзалдаггүй.
Функциональ мэдээллийн сангийн үүднээс авч үзвэл асуулга дараах байдалтай байна.

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

Уншигч та энэ асуудлыг SQL дээр бие даан шийдвэрлэхийг зөвлөж байна. Таны дуртай хүмүүсээс хамаагүй цөөн найзууд байдаг гэж үздэг. Тиймээс тэдгээрийг тусдаа хүснэгтэд оруулсан болно. Амжилттай бол хоёр одтой даалгавар бас бий. Үүнд нөхөрлөл тэгш хэмтэй байдаггүй. Функциональ мэдээллийн сан дээр дараах байдалтай харагдана.

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: эхний болон хоёр дахь одтой асуудлын шийдэл 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

дүгнэлт

Өгөгдсөн хэлний синтакс нь өгөгдсөн үзэл баримтлалыг хэрэгжүүлэх сонголтуудын нэг гэдгийг тэмдэглэх нь зүйтэй. SQL-г үндэс болгон авсан бөгөөд үүнтэй аль болох адилхан байх зорилготой байсан. Мэдээжийн хэрэг, зарим нь түлхүүр үг, үгийн бүртгэл гэх мэт нэрэнд дургүй байж магадгүй юм. Энд гол зүйл бол үзэл баримтлал нь өөрөө юм. Хэрэв хүсвэл C++ болон Python хоёрыг ижил төстэй синтакс хийж болно.

Тайлбарласан мэдээллийн сангийн үзэл баримтлал нь миний бодлоор дараахь давуу талуудтай.

  • хялбар. Энэ нь энгийн тохиолдлуудад илэрхий байдаггүй харьцангуй субъектив үзүүлэлт юм. Гэхдээ хэрэв та илүү төвөгтэй тохиолдлуудыг (жишээлбэл, одтой холбоотой асуудлууд) харвал миний бодлоор ийм асуулт бичих нь илүү хялбар байдаг.
  • Инкапсуляция. Зарим жишээн дээр би завсрын функцуудыг зарласан (жишээлбэл, зарагдсан, худалдаж авсан гэх мэт), үүнээс дараагийн функцүүд бий болсон. Энэ нь шаардлагатай бол тэдгээрээс хамааралтай функцүүдийн логикийг өөрчлөхгүйгээр тодорхой функцүүдийн логикийг өөрчлөх боломжийг олгодог. Жишээлбэл, та борлуулалт хийж болно зарагдсан огт өөр объектуудаас тооцоолсон боловч бусад логик нь өөрчлөгдөхгүй. Тийм ээ, үүнийг CREATE VIEW ашиглан RDBMS-д хэрэгжүүлж болно. Гэхдээ бүх логикийг ингэж бичвэл нэг их уншигдахуйц харагдахгүй.
  • Семантик ялгаа байхгүй. Ийм мэдээллийн сан нь функц, ангиуд (хүснэгт, талбарын оронд) дээр ажилладаг. Яг л сонгодог програмчлалын нэгэн адил (хэрэв бид арга нь харьяалагдах ангийн хэлбэрийн эхний параметртэй функц гэж үзвэл). Үүний дагуу бүх нийтийн програмчлалын хэлтэй "найзлах" нь илүү хялбар байх ёстой. Нэмж дурдахад энэ үзэл баримтлал нь илүү төвөгтэй функцийг хэрэгжүүлэх боломжийг олгодог. Жишээлбэл, та дараах операторуудыг оруулж болно:

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

  • Өв залгамжлал ба полиморфизм. Функциональ өгөгдлийн санд та CLASS ClassP: Class1, Class2 бүтцээр дамжуулан олон удамшил нэвтрүүлж, олон полиморфизмыг хэрэгжүүлж болно. Яаж гэдгийг нь дараагийн нийтлэлүүддээ бичих байх.

Хэдийгээр энэ нь зүгээр л нэг ойлголт боловч бид бүх функциональ логикийг харилцааны логик болгон хөрвүүлдэг Java хэл дээр аль хэдийн хэрэгжүүлсэн байдаг. Нэмж дурдахад, дүрслэлийн логик болон бусад олон зүйлийг түүнд маш сайн холбож өгдөг бөгөөд үүний ачаар бид бүхэл бүтэн зүйлийг олж авдаг. платформ. Үндсэндээ бид RDBMS-ийг (одоогоор зөвхөн PostgreSQL) "виртуал машин" болгон ашигладаг. RDBMS асуулга оновчтойлогч нь FDBMS-ийн мэддэг зарим статистикийг мэддэггүй тул заримдаа энэ орчуулгад асуудал гардаг. Онолын хувьд функциональ логикт тусгайлан тохируулсан тодорхой бүтцийг хадгалах сан болгон ашиглах мэдээллийн сангийн удирдлагын системийг хэрэгжүүлэх боломжтой.

Эх сурвалж: www.habr.com

сэтгэгдэл нэмэх