Mundus databases iam diu dominatus est DBMSs relationis, quae lingua SQL utuntur. Adeo ut variantes emergentes vocantur NoSQL. Locum quemdam sibi in hoc foro exsculpere curarunt, at relativa DBMSs non moritura et ad eorum usum actuose pergunt.
In hoc articulo notionem database muneris functionis describere volo. Ad meliorem intelligentiam hoc faciam comparando cum exemplaribus relationis classicis. Problemata ex variis SQL probationibus in Interreti inventas pro exemplis adhibebuntur.
introduction
Databases relationes in tabulis et agris agunt. In database munus operando, classes et functiones respective adhibebuntur. A ager in mensa cum N clavibus repraesentabitur ut functio parametri N. Loco relationum inter tabulas, functiones adhibebuntur illae reditus objectorum classium ad quas nexus fit. Munus compositio pro JOIN adhibebitur.
Priusquam directe ad munia moveatur, munus logicae domain describemus. Pro DDL utar syntaxi PostgreSQL. Nam eget syntaxin suam habet.
Tabulae et agros
Simplex Sku objectum nomen et pretium agrorum;
Relational
CREATE TABLE Sku
(
id bigint NOT NULL,
name character varying(100),
price numeric(10,5),
CONSTRAINT id_pkey PRIMARY KEY (id)
)
Eget
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
Annunciamus duos munera, quae unum modulum Sku input accipiunt et typum primitivum reddunt.
Ponitur in operando DBMS unumquodque objectum habere aliquem internum codicem qui sponte generatur et si opus sit accedere posse.
Pretium constituamus producti/copia/supplentis. Tempus mutare potest, ut tempus addamus mensae agrum. Omitabo tabulas declarandas pro directoriis relativis datorum ad minuendum codicem:
Relational
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)
)
Eget
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
indices
Ad ultimum exemplum indicem omnium clavium et tempus dabimus ut pretium certo tempore celeriter invenire possimus.
Relational
CREATE INDEX prices_date
ON prices
(skuId, storeId, supplierId, dateTime)
Eget
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
tasks
Sit scriptor satus cum simplicibus quaestionibus ex correspondente
Primo, dicamus logicam domain (nam relativa datorum hoc immediate in superiori articulo factum est).
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);
Problema 1.1
Indicem operariorum ostendunt qui salarium maius accipient quam praefecti immediati eorum.
Relational
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
Eget
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
Problema 1.2
Enumerare conductos qui maximum salarium in hac
Relational
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )
Eget
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));
Utraque opera aequiparantur. Primo in casu, in database relativo potes uti CONSPECTUM CREATORIUM, quod eodem modo primum maximum salarium pro certo genere in eo computabit. In sequentibus, ad claritatem, utar primo casu, quia melius solutionem innuitur.
Problema 1.3
Indicem department IDs praesta, numerus conductorum in quo numerus III hominum non excedit.
Relational
select department_id
from employee
group by department_id
having count(*) <= 3
Eget
countEmployees 'ΠΠΎΠ»ΠΈΡΠ΅ΡΡΠ²ΠΎ ΡΠΎΡΡΡΠ΄Π½ΠΈΠΊΠΎΠ²' (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;
Problema 1.4
Indicem operariorum qui in eadem Dicasterii parte laborantem designatum non habent.
Relational
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
Eget
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
Problema 1.5
Invenire album of department IDs cum maximo stipendio molestie totalis.
Relational
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 )
Eget
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();
Ab alio in plura negotia transeamus
Problema 2.1
Quod venditores plus quam XXX turmas producti vendidit No. 1997 in 30?
Logica domain (ut ante in RDBMS omittitur declarationem);
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);
Relational
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
Eget
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;
Problema 2.2
Pro unoquoque emptore (nomen, cognomen), duo bona inveniunt quibus emptor maximam pecuniam anno 1997 consumpsit.
Logicam domain ex antecedenti exemplo extendimus:
CLASS Customer 'ΠΠ»ΠΈΠ΅Π½Ρ';
contactName 'Π€ΠΠ' = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
Relational
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
Eget
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;
Partitio operantis hoc principium operatur: expressionem definitam post SUM (hic 1), intra determinatos circulos (hic Customer et Annus, sed aliqua vox esse potest), intra circulos sortitur expressiones in ORDINE determinatas. hic emptus et si aequalis, deinde secundum codicem internum producti).
Problema 2.3
Quot bona necessaria sunt ut a praebitoribus iubeantur ordines hodiernos adimplere.
Amplius iterum logicam domain:
CLASS Supplier 'ΠΠΎΡΡΠ°Π²ΡΠΈΠΊ';
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock 'ΠΡΡΠ°ΡΠΎΠΊ Π½Π° ΡΠΊΠ»Π°Π΄Π΅' = DATA NUMERIC[10,3] (Product);
reorderLevel 'ΠΠΎΡΠΌΠ° ΠΏΡΠΎΠ΄Π°ΠΆΠΈ' = DATA NUMERIC[10,3] (Product);
Relational
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
Eget
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;
Difficultas asterisco
Ultimum autem exemplum ex me ipso est. Est logica retis socialis. Homines possunt esse amici inter se et sicut inter se. Ex perspectiva database functionis hoc simile esset:
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
Necessarium est candidatos in amicitia invenire. Magis formaliter debes invenire omnes homines A, B, C ita ut A sit amicus cum B, B cum C, A similia C, sed A non amici cum C;
Ex perspectiva database functionis quaesitum hoc sic spectare debet:
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
friends(a, b) AND friends(b, c);
Adhortatus est lector ut hanc quaestionem SQL solvendam in suo. Ponitur quod multo pauciores sunt amici quam homines tui. Ergo sunt in tabulis separatis. Si felix, est etiam duabus stellis opus. In ea amicitia non est aequalis. In database munus munus hoc spectare debet:
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: problema cum asterisco primo et secundo from
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
conclusio,
Animadvertendum est syntaxin linguam datam unam tantum esse ex optionibus ad conceptum datum exsequendum. SQL ut basis sumpta est, et finis erat ut illi quam simillime. Utique quaedam non possunt similia nomina keywords, verbi registra, etc. Praecipua hic res est ipsum conceptum. Si placet, potes et C++ et Python syntaxin similem facere.
Descriptus notio datorum, ut opinor, haec commoda habet:
- otium. Hoc signum est relativum subiectivum quod in simplicibus casibus non patet. Sed si causas plures implicatas (exempli gratia problemata cum asteriscis spectes), multo facilius, mea sententia, huiusmodi interrogationes scribens est.
- ΠΠ½ΠΊΠ°ΠΏΡΡΠ»ΡΡΠΈΡ. In nonnullis exemplis functiones intermedias declaravi (ex. gr. vendidit, emit etc.), a quo subsequentia munera aedificata sunt. Hoc permittit ut aliquas functiones logicas mutes, si opus sit, sine mutanda logica eorum quae ab eis pendent. Exempli gratia, venditiones facere potes vendidit rentur ab omnino diversis, cetera logica non mutabuntur. Ita, hoc impleri potest in RDBMS CREATE VIEtatis usu. Sed si tota logica sic scribitur, non lectu lectu spectetur.
- Nulla semantic gap. Talis database operatur in functionibus et generibus (pro tabulis et agris). Sicut in programmatibus classicis (si ponatur methodus functionem esse cum primo parametro in forma generis cuius est). Proinde multo facilius sit "amicos facere" cum programmatibus universalibus linguarum. Praeterea conceptus hic permittit ut multo magis implicatae functiones adimpleantur. Exempli gratia, operatores quasi immersa potes:
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'ΠΠ΅ΡΡ' MESSAGE 'Π§ΡΠΎ-ΡΠΎ ΠΠ΅ΡΡ ΠΏΡΠΎΠ΄Π°Π΅Ρ ΡΠ»ΠΈΡΠΊΠΎΠΌ ΠΌΠ½ΠΎΠ³ΠΎ ΠΎΠ΄Π½ΠΎΠ³ΠΎ ΡΠΎΠ²Π°ΡΠ° Π² 2019 Π³ΠΎΠ΄Ρ';
- hereditas et polymorphismus. In database muneris functione multiplex hereditas introducere potes per CLASS ClassP: Class1, Class2 polymorphismum multiplex constructio et efficiendi. Probabiliter scribam quam exacte in articulis futuris.
Etsi conceptus hic iustus est, iam aliquam exsecutionem habemus in Java quae omnem logicam functionem in logicam relativam vertit. Plus, logica repraesentationum et multa alia pulchre ei coniuncta, quibus totum acquirimus
Source: www.habr.com