2017. január 26., csütörtök

SQL sorok összefűzése egy mezőbe FOR XML PATH segítségével


Ha az SQL-es táblában az értékek különböző sorokban vannak eltárolva, amit szeretnénk összefűzni egy mezőbe (pl. riporton helytakarékosság, felhasználóbarát formátum céljából), akkor arra több módszer is használható.

MySQL esetében erre külön függvény található: GROUP_CONCAT()
Oracle esetében: LISTAGG()
PostgreSQL esetében szintén van erre függvény: STRING_AGG()

Az MS SQL Server-en szokás szerint nincs ilyen egyszerű dolgunk. :) Az egyik leggyakoribb megoldás, amikor a FOR XML PATH-t használjuk.


1.   Ha egy táblán belül van az a mező, amire csoportosítani kell, és az a mező, amelynek értékeit össze kell fűznünk:

CREATE TABLE #Tabla
(
  Kategoria    varchar(20),
  Alkategoria varchar(20)
)

INSERT INTO #Tabla (Kategoria, Alkategoria) VALUES ('Kerékpár', 'verseny');
INSERT INTO #Tabla (Kategoria, Alkategoria) VALUES ('Kerékpár', 'túra');
INSERT INTO #Tabla (Kategoria, Alkategoria) VALUES ('Kerékpár', 'mountain bike');
INSERT INTO #Tabla (Kategoria, Alkategoria) VALUES ('Alkatrész', 'nyereg');
INSERT INTO #Tabla (Kategoria, Alkategoria) VALUES ('Alkatrész', 'pedál');

SELECT Kategoria,
       STUFF((SELECT ',' + t2.Alkategoria AS [text()] 
                FROM #Tabla t2 
               WHERE t2.Kategoria = t1.Kategoria 
                 FOR XML PATH('') ), 1, 1, '') Alkategoria
  FROM #Tabla t1
 GROUP BY Kategoria

DROP TABLE #Tabla



2.   Ha külön táblában vannak az összefűzendő értékek:

CREATE TABLE #Fej
(
  fej_id  int,
  fej_nev varchar(20)
)

INSERT INTO #Fej (fej_id, fej_nev) VALUES (1, 'Kerékpár');
INSERT INTO #Fej (fej_id, fej_nev) VALUES (2, 'Alkatrész');

CREATE TABLE #Tetel
(
  tetel_id  int,
  fej_id    int,
  tetel_nev varchar(20)
)

INSERT INTO #Tetel (tetel_id, fej_id, tetel_nev) VALUES (1, 1, 'verseny');
INSERT INTO #Tetel (tetel_id, fej_id, tetel_nev) VALUES (2, 1, 'túra');
INSERT INTO #Tetel (tetel_id, fej_id, tetel_nev) VALUES (3, 1, 'mountain bike');
INSERT INTO #Tetel (tetel_id, fej_id, tetel_nev) VALUES (4, 2, 'nyereg');
INSERT INTO #Tetel (tetel_id, fej_id, tetel_nev) VALUES (5, 2, 'pedál');

SELECT f.fej_nev,
       STUFF((SELECT ',' + t.tetel_nev [text()]
                FROM #Tetel t
               WHERE t.fej_id = f.fej_id
    FOR XML PATH('')  ), 1, 1, '' ) tetel_lista
  FROM #Fej f

DROP TABLE #Fej
DROP TABLE #Tetel







2015. december 27., vasárnap

xampp php multisite


Ha egy időben több PHP web oldalt szeretnénk/kell a gépünkön párhuzamosan fejleszteni, akkor a XAMPP segítségével ezt megtehetjük az alábbi módosításokkal.

C:\xampp\apache\conf\httpd.conf
Az alapértelmezett porton kívül vegyünk fel annyi portot, amennyi különböző web projektet szeretnénk kezelni.
Például ha a XAMPP-ban beállított alapértelmezett port a 81-es (mondjuk a 80-as port már foglalt az SSRS által), akkor a Listen 81 sor alá vegyük fel a Listen 82, Listen 83, stb sorokat. A httpd.conf fájl ide vonatkozó része így fog kinézni:

#
# Listen: Allows you to bind Apache to specific IP addresses and/or
# ports, instead of the default. See also the
# directive.
#
# Change this to Listen on specific IP addresses as shown below to
# prevent Apache from glomming onto all bound IP addresses.
#
#Listen 12.34.56.78:80
Listen 81
Listen 82
Listen 83
A fájl vége felé találjuk meg azt a bejegyzést, ami megmondja a rendszernek, hogy készüljön fel arra, hogy több projektet fogunk egyszerre használni, amit különböző portokon érünk majd el. Vegyük ki a kettős keresztet (#) a második sorból, hogy az alább látható sorokat kapjuk eredményül.

# Virtual hosts
Include conf/extra/httpd-vhosts.conf


C:\xampp\apache\conf\extra\httpd-vhosts.conf

Itt adjuk meg az egyes portokhoz tartozó beállításokat. Milyen könyvtárban keresse a php projekteket, azokat milyen porton éri el, milyen log fájlokat írjon, stb.
#
# Use name-based virtual hosting.
#
NameVirtualHost *:81
NameVirtualHost *:82
NameVirtualHost *:83
#
# VirtualHost example:
# Almost any Apache directive may go into a VirtualHost container.
# The first VirtualHost section is used for all requests that do not
# match a ServerName or ServerAlias in any block.
#

    ServerAdmin postmaster@host.localhost
    DocumentRoot "C:/xampp/htdocs/xampp"
    ServerName localhost:81
    ServerAlias www.localhost:81
    ErrorLog "logs/xampp-error.log"
    CustomLog "logs/xampp-access.log" common
 

    ServerAdmin admin@localhost
    DocumentRoot "C:/xampp/htdocs/project2"
    ServerName localhost:82
    ErrorLog "logs/project2-error.log"
    CustomLog "logs/project2-access.log" common
 

    ServerAdmin admin@localhost
    DocumentRoot "c:\xampp\htdocs\project3"
    ServerName localhost:83
    ErrorLog "logs/project3-error.log"
    CustomLog "logs/project3-access.log" common

Ezután a XAMPP control panelben (Start menü \ XAMPP \ XAMPP Control Panel) indítsuk újra az Apache modult a Stop gombbal (Actions fejléc alatt közvetlenül). Miután leállt a modul, a Stop gomb felirata Start-ra változik, klikkeljünk rá a modul újraindítására. Ha minden jól megy, akkor a Port mezőfejléc alatt megjelentek az újonnan beállított portjaink.


c:\xampp\php\php.ini

Ha az adatbázisunk MS-SQL, és Active Directory-s, LDAP bejelentkezést használunk a web oldalunkon, a következő sorokat kell aktívvá tenni, ill. hozzáírni a php.ini fájlhoz. A fájloknak természetesen létezniük kell a c:\xampp\php\ext\ könyvtárban.

extension=php_com_dotnet.dll
extension=php_mssql.dll
extension=php_pdo_mssql.dll


Angol nyelvű linkek:


2015. december 18., péntek

Dinamikus lista készítés SQL


Az alábbi lekérdezésekben megmutatom, hogyan lehet tetszőleges rekordszámú listát generálni az SQL rekurzív lekérdezésével.

Az első lekérdezés egy év, hónap listát generál tetszőlegesen megadott 2 dátum között. Továbbá megadjuk az adott hónap első és utolsó napját, illetve hány napból áll a hónap.
Lehetőség van napi bontást készíteni a lekérdezés második felében található DAY(datevalue) előtti két kötőjel eltávolításával.
Ha az eredményt később máshol szeretnénk felhasználni, betehetjük a lekérdezés futásának eredményét egy állandó, vagy átmeneti táblába (#YearMonthTempTable).

WITH mycte AS
(
  SELECT CAST('2011-01-01' AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    mycte
  WHERE   DateValue + 1 <= '2030-12-31'
)
SELECT  YEAR(datevalue) ev,
        MONTH(datevalue) honap,
--DAY(datevalue) nap,
        CONVERT(date, min(datevalue)) honap_eleje,
        CONVERT(date, max(datevalue)) honap_vege,
        CONVERT(numeric(9,0), COUNT(datevalue)) honap_napok_db
--INTO #YearMonthTempTable
FROM    mycte
group by YEAR(datevalue), MONTH(datevalue) --,DAY(datevalue)
order by YEAR(datevalue), MONTH(datevalue) --,DAY(datevalue)
OPTION (MAXRECURSION 0)
;

A második, egyszerűbb lekérdezés egy számokból álló listát készít két előre megadott szám között. Az érdekessége ennek az SQL lekérdezésnek, hogy a második oszlopban 0 és 999 közötti véletlen számokat generálunk.

WITH mycte AS
(
  SELECT 1 value
  UNION ALL
  SELECT  value + 1
  FROM    mycte
  WHERE   value + 1 <= 12
)
SELECT  value,
ABS(CHECKSUM(NewId())) % 1000 veletlen_szam
FROM    mycte
order by value
OPTION (MAXRECURSION 0)
;






2015. december 13., vasárnap

RFM elemzés


Az RFM elemzés egy marketing eszköz, amely a meglévő vevőket kategorizálja aszerint, hogy

  1. Mennyire friss a legutóbbi rendelése/vásárlása (Recency)
  2. Milyen gyakran vásárol (Frequency)
  3. Milyen értékben vásárol (Monetary)
Miért hasznos az RFM analízis?

Mert megtudhatjuk, ki az aki nagy értékben és rendszeresen vásárol, ki az aki ritkán és kis összegben. Megmondja, ki az aki rendszeresen vásárol, de egy jó ideje már adott le rendelést. Más és más megközelítéseket, marketing akciótervet kell kidolgozni ezen vevőcsoportok megszólítására.



Hogy néz ki egy ilyen elemzés elkészítése a gyakorlatban?

Csupán egy számlalistára van szükséged, amelyben benne van a vevő azonosító, a számla dátuma, és a végösszeg.

Recency: Ha megvan a fenti, számlákat tartalmazó tábla, akkor elsőként nézzük meg minden egyes vevőnél, hogy mikori volt az utolsó számla dátuma. Ezt a dátumot vonjuk ki az aktuális dátumból, és megkapjuk hogy hány nap telt el a legutóbbi vásárlás óta. Ha sok vevőnk volt, akkor rengeteg egyedi értéket kapunk, ezért ezeket soroljuk be kategóriákba. Képezzünk olyan időintervallumokat, amik üzletileg releváns tartalommal bírnak.

Frequency: Válasszunk egy tág időintervallumot az aktuális dátumtól visszanézve ( általában 1-2 év), és nézzük meg, hogy a legkorábbi és legkésőbbi vásárlás dátumok közt hány nap telt el. Ezt osszuk el a vásárlások darabszáma mínusz eggyel. Azaz ha volt két vásárlásunk, ami között egy év telt el, akkor 365 nap / (2-1) = 365 / 1 = 365 napot kapunk eredményül. A Recency-hez hasonlóan az egyes értékeket soroljuk be üzletileg jelentéssel bíró kategóriákba. Ha egy vevő csak egyszer vásárolt a kérdéses időintervallumban, akkor azt a vásárlást kezeljük külön, soroljuk be egy külön kategóriába.

Monetary: Összegezzük fel vevőnként a vizsgált időszakban a számláik végösszegét, majd ezeket az értékeket csoportosítsuk be. 

A fenti három dimenziónál a helyes osztályközök megtalálása nem feltétlenül megy elsőre, bátran kísérletezz vele. A lényeg, hogy olyan kategóriákat tudj képezni, amelyek üzletileg relevánsak, és marketing akciótervet tudsz rájuk építeni!


Angol nyelvű linkek:








2015. június 20., szombat

Staging adatbázis


Mik azok az okok, amik miatt az adatokat érdemes az áttöltések során egy köztes, úgynevezett "staging" adatbázisban eltárolni?

Az angol nyelvű Wikipedia szerint a Staging area az ETL folyamatok során használt, forrás és cél rendszerek közötti tároló hely.

Használatának legjellemzőbb okai:

  • Performacia
    A forrásrendszerekből való adatkinyerés során a performancia szűk keresztmetszet lehet. Ha már az adatexport során transzformációkat alkalmazunkaz adatokon, akkor azok rendkívül leterhelhetik a jellemzően operatív funkcókat ellátó rendszereket, és akár jelentősen lelassíthatják azok működését.
  • Változások detektálása
    Tegyük fel, hogy a forrásrendszerből mindig csak az adott hónap összes forgalmi adatát tudjuk kinyerni (mondjuk .csv formátumban), és nem csak az előző áttöltés utáni plusz rekordokat. Ekkor a teljes havi állományt ide töltjük be, és a cél rendszer és a staging adatbázis közötti eltérés áttöltésekor az áttöltés időpontját naplózva meg tudjuk állapítani, hogy az egyes értékek mikor kerültek be a cél rendszerbe.
  • Jogosultság kezelés
    A cél rendszerhez/adatbázishoz hozzáféréssel rendelkező üzleti felhasználók életét könnyítjük meg, ha csak a végleges, nekik szóló sql táblákat látják. A jogosultságok kezelése során egyszerűbb egy teljes adatbázisra jogot adni, mint az egyes táblákra.

2014. április 25., péntek

SSRS és MySQL



Feladat: 64 bites windows-os gépen szeretnénk egy távoli szerveren található MySQL adatbázishoz csatlakozni a Report Builder-rel.

Probléma: A Report Builder 32 bites alkalmazás, ezért nem jó a 64 bites MySQL driver.
Egy angol nyelvű cikk az esetről: http://blogs.msdn.com/b/sqlcat/archive/2011/03/31/32-and-64-bit-connectivity-from-the-same-machine.aspx

Megoldás: A 32 bites és 64 bites mysql odbc drivert is fel kell telepíteni az SSRS-t futtató gépre! A 32 bites a Report Builder-hez, a 64 bites drivert pedig a 64 bites adatkapcsolatot igénylő alkalmazásokhoz, amelyek lehetnek pl. SQL Server Management Studio, MS Excel, MS Access, stb.

  1. Driverek letöltése

    A driver-eket innen lehet letölteni: https://dev.mysql.com/downloads/connector/odbc/

  2. Driver-ek telepítése

    Célszerű rendszergazdai jogosultsággal telepíteni, mert - én legalábbis így jártam- ennek hiányában nem teszi fel a driver-eket.
     
  3. ODBC kapcsolatok létrehozása

    Szintén rendszergazdai jogosultsággal biztos ami biztos alapon odbc kapcsolatokat létrehozni (32 és 64 bit, teljesen ugyanazon beállításokkal). A 32 és 64 bites ODBC kapcsolatokról egy Microsoft tudástár bejegyzés: https://support.microsoft.com/en-us/kb/942976
     
  4. SSRS új Data Source létrehozása

    Az SSRS oldalon hozzuk létre az új adatforrást, amit ezután már használhatunk a Report Builder-ben, mint megosztott adatforrást. (Shared Data Source)


Linked server létrehozása:

Ha az SQL szerverhez szeretnénk csatolni egy külső, esetünkben MySQL adatbázist, akkor az 1-3 pont elvégzése után az alábbi cikkben leírtak alapján a művelet elvégezhető: http://www.packtpub.com/article/mysql-linked-server-on-sql-server-2008

Provider String mezőbe a következőt írni: DSN=OdbcKapcsolatNeve;
A többi a cikkben leírtak szerint.


2013. október 26., szombat

BCG mátrix


A BCG mátrix a termékek és szolgáltatásoknál azok növekedési potenciálja és piaci részesedése alapján vizsgálódik és helyezi el őket egy kétdimenziós mátrixban.

Segítségével a vállalat képessé válik

  • a termékek és szolgáltatások életciklusának áttekintésére,
  • a termékek és szolgáltatások piaci helyzetének feltérképezésére,
  • az optimális termékportfólió kialakítására
A vállalat a termékeket 2 szempont szerint osztályozza és helyezi el a következő 4 kategória valamelyikébe:
  • Sztárok (Star)
  • Kérdőjelek (Question)
  • Fejőstehenek (Cash Cow)
  • Döglött kutyák (Dog)


Sztárok
A sztárok a piacon a legjobb pozíciót betöltő termékek, magas piaci részesedés és magas piaci növekedés jellemzi őket, jelentős a kereslet irántuk.

Kérdőjelek
A kérdőjelek azok a viszonylag új termékek, amelyeknek még alacsony a piaci részesedésük, s a gyors piaci növekedés lehetősége jellemző rájuk. Érdemes alapos elemzés alá vonni a terméket, s ezt követően dönteni a termék sorsáról (beruházás, kivonás)

Fejőstehenek
A fejős tehenek kategóriába tartozó termékek piaci részesedése magas, azonban a piaci növekedésre már nem jellemző a magas érték. Fontos, hogy e termékek megtartsák erős piaci pozíciójukat, s a vállalat kihasználja a még bennük rejlő lehetőségeket, ugyanakkor a termékhez kapcsolódóan már nem érdemes új beruházásokat indítani.

Döglött kutyák
A döglött kutyák olyan termékek, amelyeknek sem a piaci részesedése, sem a piaci növekedési lehetősége nem kielégítő.


Linkek: