ããŒã¿ããŒã¹ã®äžçã¯é·ãéã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);
XNUMXã€çºè¡šããŸã æ©èœãå ¥åãšã㊠XNUMX ã€ã®ãã©ã¡ãŒã¿ãŒ 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ã¿ã¹ã¯
éšéIDãåŸæ¥å¡æ°ã3å以äžã®äžèŠ§ã衚瀺ããŸãã
é¢é£ãã
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ã¿ã¹ã¯
åŸæ¥å¡ã®çµŠäžç·é¡ã®æé«é¡ãèšèŒãããŠããéšé ID ã®ãªã¹ããæ€çŽ¢ããŸãã
é¢é£ãã
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();
å¥ã®ããè€éãªã¿ã¹ã¯ã«ç§»ããŸããã
2.1ã¿ã¹ã¯
1997 幎ã«è£œå No. 30 ã 1 ãŠããã以äžè²©å£²ãã販売è ã¯ã©ãã§ãã?
ãã¡ã€ã³ ããžã㯠(以åã® 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 幎ã«è³Œå ¥è ãæãå€ãã®ãéãè²»ããã XNUMX ã€ã®åå (åå) ãèŠã€ããŸãã
åã®äŸãããã¡ã€ã³ ããžãã¯ãæ¡åŒµããŸãã
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 æŒç®åã¯æ¬¡ã®ååã«åºã¥ããŠæ©èœããŸããæå®ãããã°ã«ãŒã (ããã§ã¯ Customer ãš Year ã§ãããä»»æã®åŒã䜿çšã§ããŸã) å 㧠SUM ã®åŸã«æå®ãããåŒ (ããã§ã¯ 1) ãåèšããORDER ã§æå®ãããåŒã«ãã£ãŠã°ã«ãŒãå ã䞊ã¹æ¿ããŸã (ããã§è³Œå ¥ããçããå Žåã¯ãå éšè£œåã³ãŒãã«åŸã£ãŠãã ããïŒã
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);
åæ
ã®å¯èœæ§ã®ããåè£è
ãèŠã€ããå¿
èŠããããŸããããæ£åŒã«ã¯ãA 㯠B ãšåéã§ãB 㯠C ãšåéã§ãA 㯠C ã奜ãã§ãããA 㯠C ãšåéã§ã¯ãªãããã¹ãŠã®äºº AãBãC ãèŠã€ããå¿
èŠããããŸãã
æ©èœããŒã¿ããŒã¹ã®èŠ³ç¹ããèŠããšãã¯ãšãªã¯æ¬¡ã®ããã«ãªããŸãã
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 ã§èªåã§è§£æ±ºããããšããå§ãããŸããåéã®æ°ã¯å¥œããªäººããã¯ããã«å°ãªããšæ³å®ãããŸãããããã£ãŠããããã¯å¥ã®ããŒãã«ã«ãããŸããæåãããšæXNUMXã€ã®ã¿ã¹ã¯ããããŸãããã®äžã§ãåæ ã¯å¯Ÿç§°çã§ã¯ãããŸãããæ©èœããŒã¿ããŒã¹ã§ã¯æ¬¡ã®ããã«ãªããŸãã
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: æåãš XNUMX çªç®ã®ã¢ã¹ã¿ãªã¹ã¯ã®åé¡ã®è§£æ±ºç
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
ãŸãšã
æå®ãããèšèªæ§æã¯ãæå®ãããæŠå¿µãå®è£ ããããã®ãªãã·ã§ã³ã® XNUMX ã€ã«ãããªãããšã«æ³šæããŠãã ããã SQL ãåºç€ãšããŠæ¡çšãããç®æšã¯ SQL ã«ã§ããã ã䌌ããããšã§ããããã¡ãããããŒã¯ãŒããåèªã¬ãžã¹ã¿ãªã©ã®ååãæ°ã«å ¥ããªã人ããããããããŸãããããã§éèŠãªã®ã¯ã³ã³ã»ãããã®ãã®ã§ããå¿ èŠã«å¿ããŠãC++ ãš Python ã®äž¡æ¹ãåæ§ã®æ§æã«ããããšãã§ããŸãã
ç§ã®èãã§ã¯ãããã§èª¬æããããŒã¿ããŒã¹ã®æŠå¿µã«ã¯æ¬¡ã®å©ç¹ããããŸãã
- ç·©åãããã¯æ¯èŒç䞻芳çãªææšã§ãããåçŽãªå Žåã«ã¯æããã§ã¯ãããŸãããããããããè€éãªã±ãŒã¹ (ããšãã°ãã¢ã¹ã¿ãªã¹ã¯ã®åé¡) ã«ç®ãåãããšãç§ã®æèŠã§ã¯ããã®ãããªã¯ãšãªãäœæããæ¹ãã¯ããã«ç°¡åã§ãã
- ÐМкапÑÑлÑÑОÑãããã€ãã®äŸã§ã¯ãäžéé¢æ°ã宣èšããŸãã (ããšãã°ã 売ã, è²·ã£ã ãªã©)ãããããåŸç¶ã®é¢æ°ãæ§ç¯ãããŸãããããã«ãããå¿ èŠã«å¿ããŠãç¹å®ã®é¢æ°ã«äŸåããé¢æ°ã®ããžãã¯ãå€æŽããã«ããã®é¢æ°ã®ããžãã¯ãå€æŽããããšãã§ããŸããããšãã°ã売äžãäžããããšãã§ããŸã 売ã ã¯å®å šã«ç°ãªããªããžã§ã¯ãããèšç®ããããã®ã§ãããæ®ãã®ããžãã¯ã¯å€æŽãããŸãããã¯ãããã㯠CREATE VIEW ã䜿çšã㊠RDBMS ã«å®è£ ã§ããŸãããããããã¹ãŠã®ããžãã¯ããã®ããã«èšè¿°ãããšãããŸãèªã¿ã«ãããªããŸãã
- æå³äžã®ã®ã£ããããªãããã®ãããªããŒã¿ããŒã¹ã¯ã(ããŒãã«ããã£ãŒã«ãã§ã¯ãªã) é¢æ°ãšã¯ã©ã¹ã§åäœããŸããå€å
žçãªããã°ã©ãã³ã°ãšãŸã£ããåãã§ã (ã¡ãœãããããããå±ããã¯ã©ã¹ã®åœ¢åŒã®æåã®ãã©ã¡ãŒã¿ãŒãæã€é¢æ°ã§ãããšä»®å®ããå Žå)ããããã£ãŠããŠãããŒãµã«ããã°ã©ãã³ã°èšèªããåéã«ãããã®ã¯ãã£ãšç°¡åã«ãªãã¯ãã§ããããã«ããã®æŠå¿µã«ãããããè€éãªæ©èœã®å®è£
ãå¯èœã«ãªããŸããããšãã°ã次ã®ãããªæŒç®åãåã蟌ãããšãã§ããŸãã
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'ÐеÑÑ' MESSAGE 'ЧÑП-ÑП ÐеÑÑ Ð¿ÑÐŸÐŽÐ°ÐµÑ ÑлОÑкПЌ ЌМПгП ПЎМПгП ÑПваÑа в 2019 гПЎÑ';
- ç¶æ¿ãšããªã¢ãŒãã£ãºã ãæ©èœããŒã¿ããŒã¹ã§ã¯ãCLASS ClassP: Class1ãClass2 æ§é ãéããŠå€éç¶æ¿ãå°å ¥ããå€éå€æ æ§ãå®è£ ã§ããŸããããããä»åŸã®èšäºã§ãã®æ¹æ³ã詳ããæžããŸãã
ããã¯åãªãæŠå¿µã§ããããã¹ãŠã®é¢æ°ããžãã¯ããªã¬ãŒã·ã§ãã« ããžãã¯ã«å€æãã Java ã®å®è£
ããã§ã«ãããŸããããã«ãè¡šçŸã®ããžãã¯ããã®ä»å€ãã®ãã®ãçŸããä»å±ããŠããããã®ãããã§å
šäœçãªãã®ãåŸãããŸãã
åºæïŒ habr.com