Három view-ból egy selectet.

Adatbázis: SQL, XML DB

Üdv,

Kalapáltam három view-t: 


create or replace view group_pay as  select tid,sum(amount) as amount from payments group by tid;
create or replace view group_inc as  select tid,sum(amount) as amount from incomes group by tid;
create or replace view balance_per_tenants
       as
       select group_pay.tid,
              tenants.name,
              group_inc.amount - group_pay.amount as balance
       from group_pay,group_inc,tenants
       where group_inc.tid=group_pay.tid
       and
       tenants.id=group_pay.tid;

Magyarázat: van három tábla, a tenants, payments, incomes. A tenatns a bérlők listája, a payments, incomes a fizetendő illetve befizetett összegek listája, azonosító, befizetőazonosító, dátum és összeg szerepel. Arra vagyok kíváncsi, hogy az egyes bérlők befizették-e a megfelelő összegeket. A fenti három view ezt megoldja, a select * from balance_per_tenants; marha gyorsan le is fut, ám lehet hogy jobb lenne:

- nem az sql-ben tárolni az üzleti logikát (vagy mit)
- egy selectben megoldani az egészet.

A második érdekel elsősorban, bár a filozófiai kérdés (üzleti logika sql-ben) is, de az csak haloványan. Igazság szerint csináltam egy join-os selectet, de az kb. 1,5 percig futott és rossz eredményt adott vissza :D

Update: mysql az alany.

  • 8675 megtekintés

( nagylzs v | 2012. 12. 07., p – 22:55 )

Egy select-ben megoldani: csak subselect segítégével lehet. Ennek oka, hogy az SQL-ben a "group by" -al csak egyféle csoportosítást lehet végezni. Viszont neked egyszerre kétféle csoportosítás is kellene: a payments-et és az incomes-ot külön-külön kell csoportosítani.

Ha feltétlenül egy SQL-be akarod írni, akkor:

select t.id,t.name,
(
select coalesce(sum(i.amount),0) from incomes i where i.tid=t.id
-
select coalesce(sum(p.amount),0) from payments p where p.tid=t.id
) as balance
from tenants t
order by 2

Bár nem írtad hogy milyen adatbázis kezelő. Főleg PostgreSQL-ben vagyok otthon, de szerintem a fenti SQL le fog futni MySQL-ben is.

Külön felhívnám a figyelmet a coalesce használatára. Ha ugyanis nincs befizetés, akkor az NULL lesz. És ha egy tartozásból kivonsz egy NULL értéket akkor az eredmény is NULL lesz. Ami neked NEM JÓ! Mivel a "nem volt befizetés" az nálad egyenértékű azzal, hogy nulla befizetés volt. Tehát a null-t le kell cserélni nullára. (Más szavakkal: annak is van egyenlege, aki soha nem fizetett be semmit, és annak is van egyenlege akinek soha nem volt tartozása.)

Ami a másfél perces futást illeti: meg kell nézni a query plan-t, és ez alapján lehet kitalálni hogy mi hiányzik. A fenti lekérdezéshez ezeket ajánlanám:

create index idx_payment_tid on payments(tid)
create index idx_income_tid on incomes(tid)
create index idx_tenant_name on tenants(name)

Ha ezeket az indexeket létrehozod, akkor már nem lehet olyan nagyon lassú.

Csak kis kiegészítés, ami adott esetben drámaian nagy kiegészítés is lehet (továbbra sem tudva, hogy min és mi van tárolva): ha az alaptáblákban szignifikánsan több mező van, mint a fenti view-kban, és a dbmotor és az optimalizálója elég intelligens, akkor az indexeket inkább így érdemes definiálni: 


create index idx_payment_tid on payments(tid, amount)
create index idx_income_tid on incomes(tid, amount)
create index idx_tenant_name on tenants(name, id)

Ha ui. a fenti feltételeket teljesíti a motor, akkor lényegesen nagyobb lapmennyiség indexek mentén való bejárása helyett CSAK az indexeket járja be, mert ott minden szükséges adat a rendelkezésére áll.

Húha, köszönöm a tanácsokat, ezeket majd egy frissebb állapotban emésztem. Közben megtaláltam amit eredetileg kerestem, a show create table megmutatja, hogy a view-kból mit kalapál a motor (mysql), és ez a vége: 

CREATE ALGORITHM=UNDEFINED 
DEFINER=`root`@`%` 
SQL SECURITY DEFINER 
VIEW `balance_per_tenants` AS select
  `group_pay`.`tid` AS `tid`,
  `tenants`.`name` AS `name`,
  `tenants`.`jointid` AS `jointid`,
  (`group_inc`.`amount` + `group_pay`.`amount`) AS `balance` 
from 
  ((`group_pay` join `group_inc`) join `tenants`)
where 
  ((`group_inc`.`tid` = `group_pay`.`tid`) 
     and 
  (`tenants`.`id` = `group_pay`.`tid`))

Próbáltam értelmesen formázni. Sajnos ahogy nézem, ez nem a teljes query. Ezt se fogom ma megérteni, csak betettem ide az utókornak.

Ez mondjuk PostgreSQL esetén nem igaz, ott az MVCC szemantikája miatt betölti a lapokat akkor is, ha az indexben megtalálta az értéket. Ennek oka pont a multi version control működési módjában keresendő (ami egyébként egy jó dolog, mert lehetővé teszik hogy több tranzakció párhuzamosan update-elje és lekérdezze ugyan azt a táblát. Ez egy "better than row level locking")

Ha nagyon ki akarod optimalizálni ezt a lekérdezést, akkor inkább az javaslom, hogy az index létrehozásán felül cluster-ezd. Valahogy így lenne postgresql-ben:

alter table payments cluster on idx_payment_tid
cluster table payments
alter table incomes cluster on idx_incomes_tid
cluster tabke incomes

Ennek az lesz az eredménye, hogy ennek a két táblának a sorai a lemezen olyan fizikai sorrendben lesznek tárolva, ami megfelel az adott indexnek. Ez nem 100%-os, de erre fog törekedni a rendszer. (Főleg ha autovacuum be van kapcsolva, akkor így intézi a dolgokat). Ennek egy "mellékhatása" hogy az azonos kulcsértékekhez tartozó sorok fizikailag nem lesznek szétszórva a lapok között, hanem a lehető legkevesebb lapon fognak elhelyezkedni. Ez pedig a legkevesebb számú lemez olvasási művelettel jár.

De ezt a cluster-ezést csak akkor érdemes megcsinálni, ha ezeken a táblázatokon az index scan-ek nagy része azt az indexet használja, amire a cluster-t megcsináltad. Ilyenkor viszont (tapasztalatból mondom) sokszorosára tud gyorsítani, és mellette még memóriát is megtakarít.

Viszont azt nem tudom, hogy MySQL-ben van-e cluster.

Ja meg azt se tudom hogy MySQL-nek be kell-e olvasni a táblázat sorait ahhoz, hogy megcsinálja az index scan-t, vagy elég neki az index is.

Amúgy ha sok oszlopa van a táblázatnak (nagy a row size) akkor van még egy trükk amit lehet alkalmazni: partícionálás. Egy táblázatot kettőben tárolsz. Azokat a mezőket teszed az egyikbe, amik nagyon gyakran vannak használva. A nagy méretű mezőket (pl. blob mező, hosszú szöveges mező sok random adattal stb.) kiteszed egy másik táblázatba. Ez lecsökkenti az első táblázat row size-ját, és ez is nagyon sokat tud gyorsítani a legtöbb lekérdezésen. Ehhez okosan kell partícionálni, és ahhoz meg jó esetben előre kell tudni, hogy miféle lekérdezéseket szándékozol futtatni az adataidon.

( xclusiv v | 2012. 12. 08., szo – 23:47 )

Ezzel a query-vel csak az a baj, hogy ha van olyan bérlőd, aki soha egy fillért se fizetett (noha kellett volna), na őt itt nem fogod látni, ugyanis ő nem fog szerepelni a group_inc-ben, és a rá vonatkozó sorokat kiszórod a where-ben. Left/right join lesz a barátod szerintem. 


select
	t.name
	, coalesce(p.amount, 0) as payments
	, coalesce(i.amount, 0) as incomes
	, coalesce(p.amount, 0) - coalesce(i.amount, 0) as balace
from tenants as t
left join (
	select tid, sum(amount) as amount
	from payments
	group by 1
) as p
on t.tid = p.tid
left join (
	select tid, sum(amount) as amount
	from incomes
	group by 1
) as i
on t.tid = i.tid

Lehet itt is szűrni, de - ha jól értettem - a te megoldásod _csak_ olyan sorokat adott vissza, amik mindhárom view-ban szerepeltek. Tehát ha van egy bérlőd, aki még soha nem fizetett, az nem fog megjelenni abban a query-ben, pedig ugye van, és valszeg tartozása is van.

Rakhatod inner join-nal a bérlőt és a követelést, csak a befizetést rakd hozzájuk left join-nal.

Teljesen jogos. Egyelőre a js részét kalapálgatom a csodálatos alkalmazásnak, teszteléshez most jó amit én szültem, az is, aztán ha azzal megleszek, csinálok új teszt adathalmot, olyat, amiben lesz olyan is, aki csak tartozik. Eredetileg azért se gondoltam ilyenre, mert minden új emberke kaucióval indul (amit viszont most lehet hogy külön fogok kezelni, bár még nem tudom, hogy pontosan hogyan).