Kismillió statisztikai függvény van, ami nincs SQL-ben, hogy messzire ne is menjünk: medián
SQL tök jó meg minden adat lekérdezéshez, de mihelyst picit is magasabb szintű elemzés kell, arra az SQL totál alkalmatlan.

Én kérek elnézést!

postgresql-ben egyszerűen lehet mediánt számolni.

Egy átlagos héten négy hétfő és egy péntek van (C) Diabolis

hja, most nézem MSSQL is tud, a 2017-es verzió óta, csak valami fura okból PERCENTILE_DISC-nek hívják. Mindenesetre csak egy példát akartam hozni, hogy az SQL analitikus függvényei erősen korlátosak, próbálj meg ilyen-olyan eloszlásokat számolni velük, vonalakat illeszteni, azok meredekségét figyelni stb...

Én kérek elnézést!

Emlékeim szerint Oracle-ben volt medián, de értem mire akarsz utalni és köszönöm!

Végülis rengeteg beimportálható forrás áll rendelkezésre Py-ban, míg SQL-ben a szoftver készletéből kell gazdálkodni, ami nem bővül túl sűrűn.

Mess with the best / Die like the rest

Ha megnézed, hogy ez mit tud: https://pandas.pydata.org/ vs ehhez képest az SQL mit tud, akkor nincs mit tovább magyarázni. 1-2 sor kóddal tudsz trend vonalakat illeszteni adat pontokra, ezt vizualizálni stb...

Én kérek elnézést!

egyetértek, nincs mit magyarázni, a postgresql legalább annyit vagy többet tud, mint ez a pandas. mondjuk ez így csak a statisztikai funkciókra igaz, mert ha melléteszed például a geometriai funkcióit is, akkor a python a fasorban sincs a postgresql-hez képest.

az utóbbi időben az a vélemény alakult ki bennem a postgresql-ről, hogy a legjobb, ha hagyod az adatbáziskezelőt dolgozni.

Egy átlagos héten négy hétfő és egy péntek van (C) Diabolis

Viszont nem csak postgresql van a világon (ami egyébként tényleg nem rossz). Data Science-ként sokszor nem is igazi sql-ből jönnek az adatok (lehet nosql, vagy data lake vagy bármi), azaz kell egy nyelv az sql-en kívül, amivel egységesen meg lehet valósítani a statisztikákat, elemzéseket.

Én kérek elnézést!

Sziasztok, segítséget szeretnék kérni:

Van két táblám:

Project tábla
projectID
projectName

ProjectCost tábla
projectID
costCategory
cost

Hogy tudok ebből egy olyan lekérdezést készíteni, ahol a project neve mellett felsorolásra kerülnek (külön oszlopokban) a kategóriánkénti költségek.



Fontos, hogy egy projecthez több azonos kategóriájú költség sor is tartozhat, ezek összegének kéne szerepelni a lekérdezésben.

SELECT 
    Project.ProjectName AS 'Project Name',
    cost.CostCategory1 AS 'Cost category1',
    cost.CostCategory2 AS 'Cost category2'
FROM Project
INNER JOIN ProjectCost
    ON Project.projectID = ProjectCost.projectID

Ha egy projektnek több költsége is lehet, akkor....
SELECT 
    Project.ProjectName AS 'Project Name',
    SUM(cost.CostCategory1) AS 'Cost category1',
    SUM(cost.CostCategory2) AS 'Cost category2'
FROM Project
INNER JOIN ProjectCost
    ON Project.projectID = ProjectCost.projectID
GROUP BY
    Project.ProjectName

[ Szerkesztve ]

Mess with the best / Die like the rest

Sorok oszlopokká forgatásához a PIVOT függvény kell, de azt nem minden DB kezelő ismeri.

Szintaxisa valahogy így néz ki:

SELECT p.projectName, pc.costCategory, pc.cost
FROM Project p
LEFT JOIN ProjectCost pc
ON pc.projectID=p.projectID
PIVOT(
SUM(pc.cost) sum
FOR(pc.costCategory)
IN('Cost category1', 'Cost category2', 'Cost category3', 'Cost category4')
)
ORDER BY p.projectName;

Gyakorlatilag a FOR-nál megadott costCategory mező értékkészletét válogatja szét, és csinál belőlük új oszlopokat az IN-nél megadott sorrendben, és ezekbe az oszlopokba teszi a FOR előtti oszlopfüggvény értékét.

PIVOT()-on belül sehol nem említett oszlopok (projectName) pedig maradnak úgy ahogy van.

[ Szerkesztve ]

Hello IT! Have you tried turning it off and on again?

Ez így nem jó, mivel ő az egyes costCategory alá tartozó tételek összegét külön-külön oszlopban szeretné látni.

Meg lehet csinálni PIVOT() nélkül is, oszloponként külön JOINnal:
SELECT p.projectName 'Project Name',
SUM(pc1.cost) 'Cost category1',
SUM(pc2.cost) 'Cost category2',
SUM(pc3.cost) 'Cost category3',
SUM(pc4.cost) 'Cost category4'
FROM Project p
LEFT JOIN ProjectCost pc1
ON pc1.projectID=p.projectID
AND pc1.costCategory='Cost category1'
LEFT JOIN ProjectCost pc2
ON pc2.projectID=p.projectID
AND pc2.costCategory='Cost category2'
LEFT JOIN ProjectCost pc3
ON pc3.projectID=p.projectID
AND pc3.costCategory='Cost category3'
LEFT JOIN ProjectCost pc4
ON pc4.projectID=p.projectID
AND pc4.costCategory='Cost category4'
GROUP BY p.projectName
ORDER BY p.projectName;

Itt az egyes JOINoknál szűröm a costCategory értékét, hogy az adott oszlopban melyik értékhez tartozó tételek látszanak (amiket aztán szummázunk).

PIVOT()-tal rövidebben, tömörebben lehet ugyanezt megcsinálni, viszont a mit írjak a FOR és IN részekhez megértése elsőre nehéz lehet.

[ Szerkesztve ]

Hello IT! Have you tried turning it off and on again?

Közben találtam egy másik megoldást, ami nem a JOINnál szűr, hanem CASE WHEN-ekkel pakolja külön oszlopokba az egyes tételeket:

SELECT p.projectName 'Project Name',
SUM(CASE WHEN pc.costCategory='Cost category1' THEN pc.cost ELSE 0 END) 'Cost category1',
SUM(CASE WHEN pc.costCategory='Cost category2' THEN pc.cost ELSE 0 END) 'Cost category2',
SUM(CASE WHEN pc.costCategory='Cost category3' THEN pc.cost ELSE 0 END) 'Cost category3',
SUM(CASE WHEN pc.costCategory='Cost category4' THEN pc.cost ELSE 0 END) 'Cost category4'
FROM Project p
LEFT JOIN ProjectCost pc
ON pc.projectID=p.projectID
GROUP BY p.projectName
ORDER BY p.projectName;

De ez sem sokkal olvashatóbb

Jut eszembe, hasonló példával szívatott a mostani főnököm 3 éve állásinterjún.
Aztán nemsokkal később belebotlottam kolléga kódjába, ami 10 attribútum nevét és értékét feszíti ki egy termék sorra ugyanígy
Azóta sem mertem átírni PIVOTra.

[ Szerkesztve ]

Hello IT! Have you tried turning it off and on again?

Én így szoktam, csak annyi, hogy általában egy tárolt eljárásban, ezért az első select csak kategóriákat adja vissza értékkel, a második meg oszlopokba rendezi summal. Így egy fokkal olvashatóbb.

"Debugging is like being the detective in a crime movie where you're also the murderer."

Nagyon köszönöm a segítséget. A PIVOT()-os megoldást választottam.
Még egyszer köszönöm!

Üdv!

PostgreSQL 10-ben egy függvényt írok, melyben szeretnék létrehozni egy view-t. A problémám az, hogy nem tudok a view létrehozásakor változót használni.
Tehát példuál a WHERE feltételnél: a "x BETWEEN valtozo1 AND valtozo2" rész problémás. Azt írja hibaként, hogy column "valtozo1" does not exist. Ha a select ugyanez, de nem view-t hozok létre, hanem táblát, akkor tökéletesen működik.

Van erre valami megoldás? Vagy tényleg nem lehet PostgreSQL függvényben lévő VIEW létrehozásakor változóra hivatkozni?

Ha az a cél, hogy egy külső alkalmazás paraméterezetten hívjon egy eljárást/függvényt, akkor nem tudod refcursorral visszaadni a szűrt halmazt?

Legalábbis mi Oracle 11g alapon így szoktuk visszaadni az adatokat:
procedure get_order_status(p_group_id number, p_posting_id number, p_id number, p_order_num varchar2, c out sys_refcursor) is
begin
open c for
select
i.group_id,
i.posting_id,
i.id,
i.order_num,
o.status as status,
to_char(o.status_dt,'yyyy-mm-dd hh24:mi:ss') as status_ts
from input i
left join s_order o
on o.order_num= i.order_num
where i.id = p_id
or i.group_id = p_group_id
or i.posting_id = p_posting_id
or i.order_num = p_order_num;
end;

Aztán Javaban fetchelik a kurzort.

[ Szerkesztve ]

Hello IT! Have you tried turning it off and on again?

execute?

Egy átlagos héten négy hétfő és egy péntek van (C) Diabolis

Sziasztok

Van egy tablam par VIRTUAL GENERATED oszloppal. Amikor mysqldump-al csinalok egy backupot akkor ezeknek az oszlopoknak az erteke is belekerul a backupba. Amikor probalom visszaallitani a backupot akkor viszont errort dob a mysql szerver, mivel virtual oszlopba nem lehet insertelni. Mi lehet a megoldas? A szerver verzio 5.7.18.

[ Szerkesztve ]

MySQL upgrade?

Általad említett bugot elvileg javították az 5.7.9-ben, de ezek szerint a Ti verziótokban sem jó.

Ki kéne próbálni egy jóval újabb MySQL verzióval, hogy annak a dumpja kihagyja-e a virtuális mezőket az insertek generálásakor.

Hello IT! Have you tried turning it off and on again?

Ja ugy olvastam, hogy 5.7.9 utani verziokban visszajott ez a bug, downgradelni meg nem szeretnek. Vegul ugy dontottem regex-el kiszedem a backupbol a dolgokat. Nem a legjobb megoldas, de mukodik...

Az megvan, hogy a MySql amúgy 8.0.X-nél tart?

Én kérek elnézést!

Hiaba tart 8-nal ha abban is benne van a bug (ha benne van...).
Amugy meg par ev es lassan nalunk is frissitenek.

Ezt nem is vitatom, meg lehet én is írtam ilyet. Kell a pénz. Beszállító max az elején naív, hogy minőségi terméket, szolgáltatást készíthet, de pár megrendelés után valószínűleg rájön, hogy elég olyat felvenni, ami tud valami karistolni. Nem kell, hogy elhivatott legyen. Majd kicsit feljebb tolják a projekt közben az "ajánlott gépigény" részt.

Amúgy picit ON is legyek. De nem vitaindítónak szánom, hátha kiesik valami tanulság másoknak (is).

Egyik kolléga már egy hete szenvedett valamivel. Egyszer megírta a kódot és kb. mindig ugyanazt futtatta, de 12+ óra utána megszakadt a kapcsolat (távmunka). Remélte, hogy hátha valaki más is futtat valami számításigényes feladatot és elcsíphet egy nyugodtabb időszakot.

Megnézve a kódot, kicsit átírva 38 másodpercre le lett faragva a futási idő.

Három, relatíve kicsi tábla (300e rekord) tábla lett összekapcsolva.
De a gondot az okozhatta, hogy az ON feltétel után olyan komplex feltétel volt, hogy ledobtam az ékszíjat. Valami ilyesmi lehetett:
SELECT fejléc
FROM tábla1
INNER JOIN tábla2
        ON (
              tábla1.oszlop1 LIKE '%valami%' 
 OR 
 tábla1.oszlop2 >= tábla2.oszlop1 
        OR 
 (tábla1.oszlop3 IN (SELECT tábla3.oszlop1 
       FROM tábla3 
       WHERE oszlop2 > tábla2.oszlop4)
         AND .....))

Igazából annyit módosítottam a kiraktam a feltételeket külön oszlopokba CASE WHEN-ekkel, majd utána végeztem el a kötéseket. Táblakötésbe LIKE és ennyi feltétel a korábbi tapasztalataim alapján nem túl hatékony. Bár query plan-t a kollégák nem szokták nézni, pedig sokszor hasznos lenne.

+1: Sajnos sokszor látok olyat is, hogy fejlécben van tábla úgy bekötve, hogy ott is van még egy tábla a SUBSELECT-en belül. Például
SELECT
 (SELECT oszlop1 FROM tábla2 WHERE tábla2.oszlop2 = (SELECT MAX(oszlop2) FROM tábla2)) c
FROM tábla1

[ Szerkesztve ]

Mess with the best / Die like the rest

tábla1.oszlop1 LIKE '%valami%' miatt mindig full table scan lesz, nem tud semmilyen indexet használni a joinhoz.

Míg ha tábla1.oszlop1 LIKE 'valami%' -t írnál (vagyis a string elején keresel, nem közben), akkor a tábla1.oszlop1-re tett index használható lenne, és nem kellene mindig végigolvasnia az egész táblát.

Hello IT! Have you tried turning it off and on again?

Nekem úgy mutatták az öregek, hogy a LIKE-ot kerüljem. Nagyon nem hatékony. Mondjuk eddigi munkáim során mindig be is igazolódott, hogy ha kollégákat kellett segítenem.

[ Szerkesztve ]

Mess with the best / Die like the rest

Hát ezt azért így kategorikusan nem jelenteném ki, mert pl. a LIKE 'valami%' az már használ indexet. De tény, hogy ha ez az egyetlen mentsvárad akkor lehet nem optimális az adatstruktúra.

Egy indexet több módon lehet használni.
Az oszlop1 like 'valami%' szűréshez megy az index range scan az oszlop1-en levő indexen. Ez hatékonyan működik.
Az oszlop1 like '%valami%' szűréshez szintén használható az oszlop1-en levő index, csak az már nem index range scan, hanem index fast full scan lesz, ahol az adatbázis a teljes indexet végigolvassa random sorrendben.
Az oszlop1 like '%valami%' szűréshez ha még egy order by oszlop1 is van, akkor pedig index full scan is használható, ahol az adatbázis a teljes indexet végigolvassa, de a fa struktúrát bejárva, rendezett sorrendben.
Tehát nem, nem lesz mindig full table scan, mert a 300 oszlopot tartalmazó táblára történő full scan helyett még mindig gyorsabb a csak 1 oszlopot tartalmazó és ezáltal sokkal kisebb méretű indexen a full scan.
De az utóbbi 2 már nem hatékony és ha nagy mennyiségű adaton kell szöveges keresést végrehajtani, akkor a like '%valami%' helyett ott van az Oracle Text a saját indexeivel és függvényeivel, meg a többi adatbázisnál is a text alapú indexek és keresések.

valaki tudja véletlenül, hogy xml adattípusból hogyan kell a root elemet kiszedni xpath-szal? esetleg ugyanezt konkrétan postgresql-ben?

kösz.

Egy átlagos héten négy hétfő és egy péntek van (C) Diabolis

a /rootelementname nem működik? vagy néhán helyen még ezt is írták: //
10+ éve nem használtam xpath-t

Jó estét, szervusztok!

Van két adatbázisom (MS SQL platform), az egyik egy helyi SQL szerveren, a másik Azure felhőben. Mindkét adatbázist látom az SQL Management Studioban. Hogy tudok összekapcsolni (JOIN) két táblát, ha az egyik a local serveren van, a másik meg a felhőben?

Linked servert mondanám elsőre.

Köszönöm! Végül is sikerült. Bár azt nem azt engedte az SSMS, hogy behúzzak egy Azure táblát a vieweditorba, de ha kézzel megcímeztem, akkor összetudtam kötni a helyi táblával.

Sziasztok!

Szeretnek importalni egy SQL dumpot AWS Aurora-MyAQL DB-be:
source dump: 5.7.26
linux agent: mysql Ver 14.14 Distrib 5.7.30
target rds: 5.7.mysql_aurora.2.08.1

Amikor importalni probalom, ezt a hibauzenetet kaupjuk:
ERROR 1064 (42000) at line 25: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COMMENT='Stored indicator calculations'' at line 14

A dump erre vonatkozo szakasza:

-- MySQL dump 10.13  Distrib 5.7.26, for Linux (x86_64)
--
-- Host: localhost    Database: <removed>
-- ------------------------------------------------------
-- Server version 5.7.26-enterprise-commercial-advanced-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `<removed>`
--
DROP TABLE IF EXISTS `<removed>`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `<removed>` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `starttime` bigint(10) NOT NULL,
  `endtime` bigint(10) NOT NULL,
  `contextid` bigint(10) NOT NULL,
  `sampleorigin` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `sampleid` bigint(10) NOT NULL,
  `indicator` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `value` decimal(10,2) DEFAULT NULL,
  `timecreated` bigint(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `<removed>_staendcon_ix` (`starttime`,`endtime`,`contextid`),
  KEY `<removed>_con_ix` (`contextid`)
) ENGINE=InnoDB AUTO_INCREMENT=54076 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED COMMENT='Stored indicator calculations';
/*!40101 SET character_set_client = @saved_cs_client */;

Ez pedig a parancs amivel probaljuk importalni.
Korabban a heten mukodott a dump elozo verziojaval ami 10x ekkora volt mert tele volt logokkal.

cat dbdump.sql | sed 's/ROW_FORMAT=COMPRESSED/ROW_FORMAT=/g' | sed 's/user_b/user_a/g' | time  mysql -p -h aurora-mysql-cluster.cluster-<removed>.eu-east-x.rds.amazonaws.com -u user_a dbname --max-allowed-packet=1073741824

A DBt pedig ezzel krealtam:
CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Probalunk rajonni mi lehet a baj, de minden jonak tunik szintaktikailag.
A dump 26GB.

Elore is koszonok barmilyen otletet.

szerk: elnezesd de akarhogy szerkesztem szanaszet hullik a formazas

[ Szerkesztve ]

Köszi!

miért nem veszed ki teljesen a row format utasítást?
első ránézésre az a baja, hogy nem ismer olyan sor formátumot, hogy comment.
szerintem ezt:
sed 's/ROW_FORMAT=COMPRESSED/ROW_FORMAT=/g'
írd át erre:
sed 's/ROW_FORMAT=COMPRESSED//g'

[ Szerkesztve ]

Egy átlagos héten négy hétfő és egy péntek van (C) Diabolis

Koszonom szepen ez volt a gond. Pont most ajanlotta egyik kollega is ugyanezt. Ezek szerint ures ertekkel nem lehet betolteni mert a kovetkezot nem tudja akkor ertelmezni? Most atallitottam DYNAMIC-ra egy teszt miatt de elkezdte betolteni vegulis.

Köszi!

Sziasztok!
Top lekérdezéssel kapcsolatban lenne kérdésem: arra van módszer, hogy egy lekérdezésen belül visszakapjam az összesített és rendezett halmazból az első x elemet, valamint az ezeken kívüli rekordok összesített értékét is egy plusz sorban?
Tehát pl. van egy táblám [A], [B] oszlopokkal és 1000 sorral, amikben az [A] 50 féle értéket vehet fel, a [B] pedig az aggregálandó mező. Ebből kellene pl. a TOP 5, de úgy, hogy gyakorlatilag 6 rekordot kapjak vissza: az utolsóban annak a 45 féle [A] értéknek az összesenjével, ami egyébként nem fért bele a topba.
A nagyságrend elégé változó, de az [A] értékeket tekintve több 10e simán előfordulhat és a top is kb. tetszőleges, ezért kerülném a kétlépcsős megoldást (ha lehet).
Firebird 2.5 és 3, MsSQL, Oracle megoldás érdekelne.

My story is one of many thousands, and the world will not suffer if it ends too soon.

Én kiemelném egy CTEbe az A, B összege oszlopokat, és kapna egy sorszámot összeg szerint csökkenő sorrendben, majd következő lépésben ebből válogatnám le a sorszám<=5-öt ("top 5"), és hozzáunióznám a szumma(B összeg)-et, ahol sorszám>5.

Így az eredeti táblát csak egyszer kell végigolvasni, második lépésben uniót képző 2 select már a memóriában lévő párszáz-ezer soros aggregátumból dolgozik, minimális többletköltséggel.

Valahogy így:
with summa as (
select a,
sum(b) sum_b,
row_number() over (partition by a order by sum(b) desc) rn
from tabla5
group by a),
top5_summa as (
select a,
sum_b,
rn
from summa
where rn<=5
union
select 'Többi' as a,
sum(sum_b) as sum_b,
6 as rn
from summa
where rn>5)

select a,
sum_b
from top5_summa
order by rn;

Nem tudom, Firebird ismeri-e ezt a szintaxist, SQL Server kb. 2005 óta igen, meg az Oracle 11 alatt is működik.

[ Szerkesztve ]

Hello IT! Have you tried turning it off and on again?

Köszönöm!
Ismeri a 2.5 is, az analitikus függvények viszont ha jól emlékszem csak 3 -tól vannak, de CTE -vel más módon szerintem megoldom majd. Csak olyan ritkán kell DB -t piszkálnom, hogy már elfelejtem melyik mit is tud

My story is one of many thousands, and the world will not suffer if it ends too soon.

Sziasztok, kezdő vagyok SQL terén, már 2-3 órát beleöltem a következő feladatba, esetleg tudna segíteni benne valaki?

Az items táblából szeretném lekérni az olyan sorokat aminek az ID-je csak 1x szerepel a táblában majd a kapott tömbből szeretném ha ez a két feltétel teljesülne: status = open, type = 477.

Előre is nagyon szépen köszönöm a segítséget.

Group by és having

Én kérek elnézést!