Dunia pangkalan data telah lama dikuasai oleh DBMS hubungan, yang menggunakan bahasa SQL. Sehinggakan varian yang muncul dipanggil NoSQL. Mereka berjaya mengukir tempat tertentu untuk diri mereka sendiri dalam pasaran ini, tetapi DBMS hubungan tidak akan mati, dan terus digunakan secara aktif untuk tujuan mereka.
Dalam artikel ini saya ingin menerangkan konsep pangkalan data berfungsi. Untuk pemahaman yang lebih baik, saya akan melakukan ini dengan membandingkannya dengan model hubungan klasik. Masalah daripada pelbagai ujian SQL yang terdapat di Internet akan digunakan sebagai contoh.
Pengenalan
Pangkalan data hubungan beroperasi pada jadual dan medan. Dalam pangkalan data berfungsi, kelas dan fungsi akan digunakan masing-masing. Medan dalam jadual dengan kekunci N akan diwakili sebagai fungsi parameter N. Daripada perhubungan antara jadual, fungsi akan digunakan yang mengembalikan objek kelas yang sambungan dibuat. Komposisi fungsi akan digunakan dan bukannya JOIN.
Sebelum beralih terus ke tugasan, saya akan menerangkan tugas logik domain. Untuk DDL saya akan menggunakan sintaks PostgreSQL. Untuk fungsi ia mempunyai sintaksnya sendiri.
Jadual dan medan
Objek Sku mudah dengan medan nama dan harga:
perhubungan
CREATE TABLE Sku
(
id bigint NOT NULL,
name character varying(100),
price numeric(10,5),
CONSTRAINT id_pkey PRIMARY KEY (id)
)
Berfungsi
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
Kami mengumumkan dua fungsi, yang mengambil satu parameter Sku sebagai input dan mengembalikan jenis primitif.
Diandaikan bahawa dalam DBMS berfungsi setiap objek akan mempunyai beberapa kod dalaman yang dijana secara automatik dan boleh diakses jika perlu.
Jom set harga produk/kedai/pembekal. Ia mungkin berubah mengikut masa, jadi mari tambah medan masa pada jadual. Saya akan melangkau mengisytiharkan jadual untuk direktori dalam pangkalan data hubungan untuk memendekkan kod:
perhubungan
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)
)
Berfungsi
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
Indeks
Untuk contoh terakhir, kami akan membina indeks pada semua kunci dan tarikh supaya kami dapat mencari harga dengan cepat untuk masa tertentu.
perhubungan
CREATE INDEX prices_date
ON prices
(skuId, storeId, supplierId, dateTime)
Berfungsi
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
tugas-tugas
Mari kita mulakan dengan masalah yang agak mudah diambil dari yang sepadan
Pertama, mari kita isytiharkan logik domain (untuk pangkalan data hubungan ini dilakukan secara langsung dalam artikel di atas).
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);
Tugasan 1.1
Paparkan senarai pekerja yang menerima gaji lebih besar daripada penyelia terdekat mereka.
perhubungan
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
Berfungsi
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
Tugasan 1.2
Senaraikan pekerja yang menerima gaji maksimum di jabatan mereka
perhubungan
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )
Berfungsi
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));
Kedua-dua pelaksanaan adalah setara. Untuk kes pertama, dalam pangkalan data hubungan anda boleh menggunakan CREATE VIEW, yang dengan cara yang sama akan terlebih dahulu mengira gaji maksimum untuk jabatan tertentu di dalamnya. Dalam perkara berikut, untuk kejelasan, saya akan menggunakan kes pertama, kerana ia lebih mencerminkan penyelesaiannya.
Tugasan 1.3
Paparkan senarai ID jabatan, bilangan pekerja yang tidak melebihi 3 orang.
perhubungan
select department_id
from employee
group by department_id
having count(*) <= 3
Berfungsi
countEmployees 'ΠΠΎΠ»ΠΈΡΠ΅ΡΡΠ²ΠΎ ΡΠΎΡΡΡΠ΄Π½ΠΈΠΊΠΎΠ²' (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;
Tugasan 1.4
Paparkan senarai pekerja yang tidak mempunyai pengurus yang ditetapkan bekerja di jabatan yang sama.
perhubungan
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
Berfungsi
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
Tugasan 1.5
Cari senarai ID jabatan dengan jumlah gaji pekerja maksimum.
perhubungan
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 )
Berfungsi
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();
Mari kita beralih kepada tugas yang lebih kompleks daripada tugas yang lain
Tugasan 2.1
Penjual manakah yang menjual lebih daripada 1997 unit produk No. 30 pada tahun 1?
Logik domain (seperti sebelum ini pada RDBMS kami melangkau perisytiharan):
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);
perhubungan
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
Berfungsi
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;
Tugasan 2.2
Untuk setiap pembeli (nama, nama keluarga), cari dua barang (nama) yang pembeli membelanjakan wang paling banyak pada tahun 1997.
Kami memperluaskan logik domain dari contoh sebelumnya:
CLASS Customer 'ΠΠ»ΠΈΠ΅Π½Ρ';
contactName 'Π€ΠΠ' = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
perhubungan
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
Berfungsi
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;
Operator PARTITION berfungsi berdasarkan prinsip berikut: ia menjumlahkan ungkapan yang dinyatakan selepas SUM (di sini 1), dalam kumpulan yang ditentukan (di sini Pelanggan dan Tahun, tetapi boleh menjadi sebarang ungkapan), mengisih dalam kumpulan mengikut ungkapan yang dinyatakan dalam ORDER ( di sini dibeli, dan jika sama, maka mengikut kod produk dalaman).
Tugasan 2.3
Berapa banyak barang yang perlu ditempah daripada pembekal untuk memenuhi tempahan semasa.
Mari kembangkan logik domain sekali lagi:
CLASS Supplier 'ΠΠΎΡΡΠ°Π²ΡΠΈΠΊ';
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock 'ΠΡΡΠ°ΡΠΎΠΊ Π½Π° ΡΠΊΠ»Π°Π΄Π΅' = DATA NUMERIC[10,3] (Product);
reorderLevel 'ΠΠΎΡΠΌΠ° ΠΏΡΠΎΠ΄Π°ΠΆΠΈ' = DATA NUMERIC[10,3] (Product);
perhubungan
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
Berfungsi
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;
Masalah dengan asterisk
Dan contoh terakhir adalah dari saya sendiri. Terdapat logik rangkaian sosial. Orang boleh berkawan antara satu sama lain dan suka antara satu sama lain. Dari perspektif pangkalan data berfungsi ia akan kelihatan seperti ini:
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
Ia adalah perlu untuk mencari calon yang mungkin untuk persahabatan. Secara lebih formal, anda perlu mencari semua orang A, B, C supaya A berkawan dengan B, dan B berkawan dengan C, A suka C, tetapi A tidak berkawan dengan C.
Dari perspektif pangkalan data berfungsi, pertanyaan akan kelihatan seperti ini:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
friends(a, b) AND friends(b, c);
Pembaca digalakkan untuk menyelesaikan masalah ini dalam SQL sendiri. Diandaikan bahawa terdapat lebih sedikit rakan daripada orang yang anda suka. Oleh itu mereka berada dalam jadual berasingan. Jika berjaya, ada juga tugasan dengan dua bintang. Di dalamnya, persahabatan tidak simetri. Pada pangkalan data berfungsi ia akan kelihatan seperti ini:
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: penyelesaian kepada masalah dengan asterisk pertama dan kedua daripada
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
Kesimpulan
Perlu diingatkan bahawa sintaks bahasa yang diberikan hanyalah salah satu pilihan untuk melaksanakan konsep yang diberikan. SQL telah diambil sebagai asas, dan matlamatnya adalah untuk menjadi sama yang mungkin dengannya. Sudah tentu, sesetengah mungkin tidak menyukai nama kata kunci, daftar perkataan, dll. Perkara utama di sini ialah konsep itu sendiri. Jika mahu, anda boleh membuat kedua-dua C++ dan Python sintaks yang serupa.
Konsep pangkalan data yang diterangkan, pada pendapat saya, mempunyai kelebihan berikut:
- Kesederhanaan. Ini adalah penunjuk yang agak subjektif yang tidak jelas dalam kes mudah. Tetapi jika anda melihat kes yang lebih kompleks (contohnya, masalah dengan asterisk), maka, pada pendapat saya, menulis pertanyaan sedemikian adalah lebih mudah.
- ΠΠ½ΠΊΠ°ΠΏΡΡΠ»ΡΡΠΈΡ. Dalam beberapa contoh saya mengisytiharkan fungsi perantaraan (contohnya, dijual, dibeli dsb.), dari mana fungsi berikutnya dibina. Ini membolehkan anda menukar logik fungsi tertentu, jika perlu, tanpa mengubah logik fungsi yang bergantung padanya. Sebagai contoh, anda boleh membuat jualan dijual dikira daripada objek yang sama sekali berbeza, manakala logik yang lain tidak akan berubah. Ya, ini boleh dilaksanakan dalam RDBMS menggunakan CREATE VIEW. Tetapi jika semua logik ditulis dengan cara ini, ia tidak akan kelihatan sangat boleh dibaca.
- Tiada jurang semantik. Pangkalan data sedemikian beroperasi pada fungsi dan kelas (bukannya jadual dan medan). Sama seperti dalam pengaturcaraan klasik (jika kita menganggap bahawa kaedah adalah fungsi dengan parameter pertama dalam bentuk kelas yang dimilikinya). Oleh itu, lebih mudah untuk "berkawan" dengan bahasa pengaturcaraan universal. Selain itu, konsep ini membolehkan fungsi yang lebih kompleks dilaksanakan. Sebagai contoh, anda boleh membenamkan pengendali seperti:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'ΠΠ΅ΡΡ' MESSAGE 'Π§ΡΠΎ-ΡΠΎ ΠΠ΅ΡΡ ΠΏΡΠΎΠ΄Π°Π΅Ρ ΡΠ»ΠΈΡΠΊΠΎΠΌ ΠΌΠ½ΠΎΠ³ΠΎ ΠΎΠ΄Π½ΠΎΠ³ΠΎ ΡΠΎΠ²Π°ΡΠ° Π² 2019 Π³ΠΎΠ΄Ρ';
- Pewarisan dan polimorfisme. Dalam pangkalan data berfungsi, anda boleh memperkenalkan berbilang warisan melalui binaan CLASS ClassP: Class1, Class2 dan melaksanakan pelbagai polimorfisme. Saya mungkin akan menulis bagaimana tepatnya dalam artikel akan datang.
Walaupun ini hanyalah satu konsep, kami sudah mempunyai beberapa pelaksanaan di Jawa yang menterjemahkan semua logik berfungsi kepada logik hubungan. Selain itu, logik representasi dan banyak perkara lain dilampirkan dengan indah, berkat yang kami dapati keseluruhannya
Sumber: www.habr.com