Mysql Bewegende Gemiddelde Query

Voorheen het ons bespreek hoe om te skryf aan die rol gemiddeldes in Postgres. Deur die gewilde vraag is jy wys hoe om dieselfde te doen in MySQL en SQL Server. Wel dek hoe om lawaaierige kaarte annoteer soos volg: Met 'n 7-dag voor gemiddelde lyn soos volg: Die Big Idea Ons eerste grafiek hierbo is redelik raserig en hard om bruikbare inligting uit te kry. Ons kan dit glad deur die plot 'n 7-dag gemiddeld bo-op die onderliggende data. Dit kan gedoen word met venster funksies, self-sluit, of gekorreleer subqueries - goed dekking van die eerste twee. Wel begin met 'n voorafgaande gemiddelde, wat beteken dat die gemiddelde punt op die 7de van die maand is die gemiddelde van die eerste sewe dae. Visueel hierdie skuif die spykers in die grafiek om die reg, as 'n groot piek is gemiddeld oor die volgende sewe dae. Eerstens, maak 'n Intermediêre Telling Table Ons wil 'n gemiddelde bereken oor die totale aanmeldingen vir elke dag. Die aanvaarding van ons het 'n tipiese gebruikers tafel met 'n ry per nuwe gebruiker en 'n tyd stempel createdat, kan ons ons totaal te skep ons aanmeldingen tabel soos so: In Postgres en SQL Server jy kan dit gebruik as 'n CTE. In MySQL kan jy dit stoor as 'n tydelike tabel. Postgres Rolling Gemiddeld Gelukkig Postgres het venster funksies wat die eenvoudigste manier om 'n lopende gemiddelde bereken is. Hierdie navraag aanvaar dat die datums nie gapings het. Die navraag is gemiddeld oor die afgelope sewe rye, nie die afgelope sewe datums. As jou data het gapings, vul dit aan met generateseries of by teen 'n tafel met 'n digte datum rye. MySQL Rolling Gemiddeld MySQL ontbreek venster funksies, maar ons kan 'n soortgelyke berekening met behulp doen self-sluit. Vir elke ry in ons telling tafel, sluit ons elke ry wat binne die afgelope sewe dae en neem die gemiddelde. Hierdie navraag hanteer outomaties datum gapings, as ons kyk na rye binne 'n tydperk eerder as die voorafgaande N rye. SQL Server Rolling Gemiddeld SQL Server het venster funksies, sodat die berekening van die rollende gemiddelde gedoen kan word in óf die Postgres styl of MySQL styl. Vir eenvoud, was die gebruik van die MySQL weergawe met 'n self aan te sluit. Dit is konseptueel dieselfde as in MySQL. Die enigste vertalings is die dateadd funksie en uitdruklik genoem groep kolomme. Ander Gemiddeldes Ons fokus op die 7-dag sleep gemiddelde in hierdie pos. As ons wou om te kyk na die 7-dag voorste gemiddelde, sy so eenvoudig as in die ander rigting te sorteer die datums. As ons wou om te kyk na 'n gesentreerde gemiddelde, wed gebruik: Postgres: rye tussen 3 voorafgaande en 3 volgende MySql: tussen signups. date - 3 en signups. date 3 in MySQL SQL Server: tussen dateadd (dag, -3, aanmeldingen. datum) en dateadd (dag, 3, signups. date) Dit is 'n immergroen Joe Celko vraag. Ek ignoreer wat DBMS platform gebruik word. Maar in elk geval Joe was in staat om meer as 10 jaar gelede antwoord met standaard SQL. Joe Celko SQL Puzzles en Antwoorde aanhaling: Daardie laaste update poging dui daarop dat ons die gesegde kan gebruik om 'n navraag wat vir ons 'n bewegende gemiddelde sou gee te bou: Is die ekstra kolom of die navraag benadering beter Die navraag is tegnies beter omdat die werk benadering denormalize die databasis. Maar, as die historiese data aangeteken is nie van plan om te verander en die berekening van die bewegende gemiddelde is duur, kan jy oorweeg om die benadering kolom. SQL legkaart navraag: deur al beteken uniform. Jy gooi net om die toepaslike gewig emmer, afhangende van die afstand vanaf die huidige tyd punt. Byvoorbeeld quottake weight1 vir datapunte binne 24 uur van die huidige datapoint weight0.5 vir datapunte binne 48hrsquot. Daardie geval is dit sake hoeveel opeenvolgende datapunte (soos 06:12 en 23:48) is ver van mekaar 'n gebruik geval ek kan dink sou 'n poging om die histogram waar datapunte is nie dig genoeg uitvoering maak msciwoj 27 Mei 15 stryk op wees 22:22 Ek is nie seker dat jou verwagte resultaat (uitset) toon klassieke eenvoudige bewegende (rollende) gemiddeld vir 3 dae. Omdat, byvoorbeeld, die eerste trippel van getalle per definisie gee, maar jy verwag 4,360 en sy verwarrend. Nietemin, ek stel voor die volgende oplossing, wat venster-funksie AVG gebruik. Hierdie benadering is baie meer doeltreffend (duidelik en minder hulpbron-intensiewe) as self aansluit bekendgestel in ander antwoorde (en Im verbaas dat niemand 'n beter oplossing gegee). Jy sien dat AVG is toegedraai met geval wanneer ROWNUM GT p. days dan NULL s dwing in die eerste rye, waar 3 daagse bewegende gemiddelde is betekenisloos. Hy antwoord 23 Februarie by 13:12 Ons kan aansoek doen Joe Celkos vuil links buitenste deel metode (soos hierbo aangehaal deur Diego Scaravaggi) om die vraag te beantwoord soos dit gevra is. Genereer die versoek afvoer: geantwoord 9 Januarie by 00:33 Jou Antwoord 2016 stapel Exchange, IncHow 'n SQL Bereken bewegende gemiddelde sonder 'n wyser Update: As jy besig is met die nuutste weergawes van SQL Server, kan jy die windows funksies gebruik om te bereik dieselfde ding. Ek gepos word om die updated kode aan die einde van die post. Om hierdie video, ek nog graag die denkproses van anker om 'n datum. Video: 3 daagse bewegende gemiddelde in SQL 'n doeltreffende manier om 'n bewegende gemiddelde in SQL te bereken met behulp van 'n paar truuks op datum ankers stel. Daar is debatte oor die beste manier om 'n SQL bewegende gemiddelde in SQL Server doen. Sommige mense dink daar is tye wanneer 'n wyser is mees doeltreffende. Ander dink dat jy dit alles kan doen in 'n stel wat gebaseer is weg sonder die wyser. Die ander dag het ek gaan 'n bewegende gemiddelde te bereken en my eerste gedagte was om 'n wyser gebruik. Ek het 'n paar vinnige navorsing en het gevind dat hierdie forum vraag: Moving Gemiddelde in TSQL Daar is 'n pos wat 'n subquery met 'n anker datum te help vind die 1 en 2 dag geneutraliseer toon. Hier is die skrif wat jy kan gebruik om die 3 dag SQL Gemiddeld finale uitslag Moving toets. Hier is die finale navraag. Hier is die vraag wat jy sou gebruik met SQL Server 2012. Deel hierdie: AVG (Transact-SQL) ALLE Pas die totale funksie om alle waardes. Alles is die verstek. DISTINCT dui daarop dat, AVG uitgevoer word slegs op elke unieke geval van 'n waarde, ongeag hoeveel keer die waarde voorkom. uitdrukking is 'n uitdrukking van die presiese numeriese of geskatte numeriese data tipe kategorie, behalwe vir die tipe bietjie data. Totaal funksies en subqueries is nie toegelaat nie. OOR (partitionbyclause orderbyclause) partitionbyclause verdeel die resultaat stel wat deur die VANAF klousule in mure waaraan die funksie toegepas word. As nie gespesifiseer, die funksie behandel alle rye van die navraag gevolg gestel as 'n enkele groep. orderbyclause bepaal die logiese volgorde waarin die operasie uitgevoer word. orderbyclause word vereis. Vir meer inligting, sien OOR klousule (Transact-SQL). Die tipe terugkeer word bepaal deur die tipe van die geëvalueer gevolg van uitdrukking. desimale kategorie (p, s) As die tipe data van uitdrukking is 'n alias data tipe, die soort opbrengs is ook van die tipe alias data. Maar, as die tipe basis data van die alias datatipe bevorder, byvoorbeeld uit tinyint om Int. die terugkeer waarde is van die bevorder datatipe en nie die alias datatipe. AVG () bere die gemiddelde van 'n stel waardes wat deur die som van die waardes te deel deur die telling van nonnull waardes. As die som groter as die maksimum waarde vir die tipe data van die terugkeer waarde 'n fout sal teruggestuur word. AVG is 'n deterministiese funksie wanneer dit gebruik word sonder die oor en ORDER BY klousules. Dit is deterministiese wanneer gespesifiseerde met die oor en ORDER BY klousules. Vir meer inligting, sien Deterministiese en deterministiese funksies. A. Die gebruik van die som en AVG funksies vir berekeninge Die volgende voorbeeld word bereken dat die gemiddelde vakansie-ure en die som van siekteverlof ure wat die vise-presidente van Avontuur Werke Cycles gebruik. Elkeen van hierdie totaal funksies produseer 'n enkele opsomming waarde vir al die opgespoor rye. Die voorbeeld gebruik die AdventureWorks2012 database. Show net een kind ry per ouer ry Gegewe tafels ouer (ID int nie nul primêre sleutel, ens) en kind (ID int nie nul primêre sleutel, pid int nie nul verwysings ouer (OD), ens .). hoe kan ons skryf 'n navraag wat net een kind ry per pid selfs wanneer die kind tafel het verskeie bypassende rye gekry MySQL permitte gebruik van GROEP DEUR selfs wanneer die SELECT lys spesifiseer nie totaal funksie, so dit sal werk: Kies p. id, c. id van 'n ouer p aansluit kind c op p. idc. pid groep p. id Maar is dit akkuraat Nee, omdat dit slegs die eerste c. pid waarde dit gebeur om te vind vertoon. Vir verdere bespreking sien Binne-groep gemiddeldes. Slaan herhaal waardes Jy wil almal uniek waardes van 'n kolom te meld en slaan al rye herhaal enige van hierdie waardes. KIES Kol UIT cat GROUP BY Kol totale en subtotale - net Jy het 'n tabel wat die bywoning uur spore. drop tafel as bestaan ​​t skep tafel t (d datum, ID int, ure int) voeg in t waardes (2013-10-1,1,5), (2013-10-1,2,6), (2013-10 -1,3,2), (2013-10-1,3,5), (2013-10-2,1,1), (2013-10-2,1,2), (2013/10/02 , 2,3), (2013-10-2,2,4), (2013-10-3,1,2), (2013-10-3,1,2), (2013-10-3,1 , 2). en jy 'n bywoning opsomming volgens datum en persoon nodig. Dit blyk dat hierdie Aggregating navraag is maklik bo writemdashfirst n innerlike soektog na groep datum en persoon met Rollup skryf, skryf dan 'n buitenste navraag dat die Rollup Null etikette herbenoem. Track staat verander Jy het 'n tafel met 'n reeks van ID's, state en datums. Die navraag vereiste is om 'n lys van die staat veranderinge en hul datums na datum, en tel gevalle van elke opeenvolgende staat. Hierdie data van 'n blog inskrywing deur 'n Russiese MySQLer homself roep Quassnoi. ----------------------------- ID Staat Date Time ------------------ ----------- 12 1 2009-07-16 10:00 45 2 2009-07-16 13:00 67 2 2009-07-16 14:40 77 1 2009-07-16 15: 00 89 1 2009-07-16 15:30 99 1 2009-07-16 16:00 ----------------------------- waardes gekoppel met alle waardes van 'n ander kolom Jy het 'n tabel op waarin elke ry verwys na een teks en 'n navraag in die teks. DROP TABLE indien bestaan ​​sleutelwoorde CREATE TABLE sleutelwoorde (txtID int, navraag kar (8)) voeg in dokumente WAARDES (1. Cat), (2. Bar), (1. Cat), (2. Cat). en jy wil 'n lys van tekste wat elke navraag in te sluit. Jy mag dalk dink jy het om aan te sluit en wedstryd. Jy hoef. Al wat jy hoef te doen is tel die verskillende dokumente wat voorkom vir elke teks, dan vir elke teks vergelyk dat die getal met die hele lys van afsonderlike sleutelwoorde: KIES txtID, COUNT (DISTINCT navraag) AS N UIT sleutelwoorde GROUP BY txtID WAT N (SELECT COUNT (DISTINCT navraag) VAN sleutelwoorde) ---------- txtID n ---------- 2 2 ---------- Binne-groep aggregate Jy het 'n produkte tafel met kolomme item, verskaffer, prys. Veelvuldige verskaffers bied verskillende pryse vir dieselfde item. Jy moet die verskaffer met die laagste prys vir elke item te vind. DROP TABLE indien bestaan ​​produkte CREATE TABLE produkte (item int, verskaffer int, prys desimale (6,2)) voeg in produkte WAARDES (1,1,10), (1,2,15), (2,2,20) , (2,1,21), (2,2,18) kies uit produkte ----------------------- item verskaffer prys ----- ------------------ 1 1 10.00 1 2 15.00 2 2 20.00 2 1 21.00 rangorde Sonder MSSQLs rang () totaal funksie, hoe kan ons rangorde vertoon in 'n MySQL navraag byvoorbeeld uit 'n tabel soos hierdie CREATE TABLE stemme (naam CHAR (10), stem INT) voeg in stemme WAARDES (Smith, 10), (Jones, 15), (White, 20), (Swart, 40), ( groen, 50), (Brown, 20) die navraag is 'n twee-stap: 1. Sluit die tabel om homself op die waarde wat ingedeel, die hantering van bande 2. Groep en bestel die gevolg van die self-sluit op rang: skuinsstrepe in data skuinsstrepe vermeerder uitgesoekte: KIES AB RLIKE AB terug 1, net soos. KIES AB RLIKE AB want in 'n paar van skuinsstrepe, is die tweede nie ontsnap deur die eerste, so twee vasgekodeerde jy vergelyk dubbele elke agteroorskuinsstreep in die RLIKE argument. Maar as jy bevraagteken 'n tafel vir so 'n string van die MySQL kliënt, hierdie verdubbeling gebeur twee keer - een keer in die kliënt, en een keer in die databasis - so 'n kolom waarde wat ooreenstem met die AB te vind. wat jy nodig het om te skryf. SELECT desc van XXX WAAR desc RLIKE AABB Dis agt skuinsstrepe te pas twee Vergelyk data in twee tafels Hierdie navraag UNIE se navrae vir wat ooreenstem met die kolom name van twee tafels gegryp en hou net die rye wat een keer in die Unie voorkom. Dit is die rye met ongeëwenaard data. Pas jou lys kolom as jy wil, maar gewoonlik sal jy wil om dit te begin met die primêre sleutel: KIES MIN (TableName) as TableName, id, col1, col2, col3. UIT (kies Table n as TableName, a. id, a. col1, a. col2, a. col3. Uit 'n Unie Alle SELECT Tabel B as TableName, b. id, b. col1, b. col2, b. col3. UIT b) AS tmp GROUP BY ID, col1, col2, col3. WAT COUNT () 1 ORDER BY 1 Vergelyk strukture van twee tabelle om kolomme by die naam en ordinale posisie vergelyk in tabelle test. t1 en test. t2: KIES MIN (TableName) AS Table, columnname AS kolom, ordinalposition AS posisie (SELECT T1 as TableName, columnname, ordinalposition UIT informationschema. columns aS i1 WAAR tableschematest EN tablenamet1 Vergelyk twee databasisse Een van EF Codds reëls vir relasionele databasisse is die nie-agterdeur reël: alle inligting oor tabelle moet toeganklik wees slegs deur 'n navraag op tafels. sedert weergawe 5, die MySQL implementering van informationschema (iS) help voldoen Codds vereiste. iS voorrade metadata in tafels, sodat sy die eerste plek om te kyk hoe die strukture vergelyk van twee databasisse. Elders op hierdie bladsy is daar 'n eenvoudige navraag sjabloon vir die vergelyking van data in twee struktureel soortgelyke tabelle: KIES MIN (TableName) as TableName, id, col1, col2, col3 UIT (SELECT Table n as TableName, a. id, a. col1, a. col2, a. col3 UIT.. 'n Unie Alle SELECT Tabel b as TableName, b. id, b. col1, b. col2, b. col3. UIT b) AS tmp databasis grootte SELECT tableschema AS Db Naam, Round (Som (datalength indexlength) / 1024/1024, 3) AS Db Grootte (MB), Ronde (Som (datafree) / 1024/1024, 3) as vrye ruimte (MB) vAN informationschema. tables GROUP bY tableschema Vind die grootte van die hele databasis op die bediener Dit is gebaseer op 'n navraag Mark Leith gepos word aan die lys MySQL Algemene bespreking. DROP OOG indien bestaan ​​dbsize SKEP VIEW dbsize AS SELECT s. schemaname AS skedule, SUM (t. datalength) AS Data, SUM (t. indexlength) AS indekse, SUM (t. datalength) som (t. indexlength) AS MB Gebruikte, INDIEN (som (t. datafree) 0,, SUM (t. datafree)) Soos MB Free, INDIEN (som (t. datafree) 0,, 100 (som (t. datalength) som (t. indexlength)) / ( (som (t. datalength) som (t. indexlength) som (IFNULL (t. datafree, 0))))) AS PCT Gebruikte, COUNT (tablename) AS tabelle uit informationschema. schemata s links JOIN informationschema. tables t OP se. schemaname t. tableschema GROUP BY s. schemaname MET Rollup Lys databasisse, tafels, kolomme SELECT t. tableschema AS databasis, t. tablename AS Table, t. tabletype AS Tabel Tipe, c. columnname AS kolom, c. datatype AS Data Tipe UIT informationschema. tables t JOIN informationschema. columns c OP t. tableschema c. tableschema EN t. tablename c. tablename WAAR t. tableschema NIE IN (mysql, informationschema) ORDER BY t. tableschema, t. tabletype, t. tablename, c. ordinalposition Vind kruis-databasis vreemde sleutels KIES u. tableschema as RefSchema, u. tablename as RefTable, u. columnname as RefColumn, u. referencedtableschema as skedule, u. referencedtablename as Table, u. referencedcolumnname as kolom van informationschema. tableconstraints aS c JOIN informationschema. keycolumnusage AS u GEBRUIK (constraintschema, constraintname) WAAR c. constrainttypeFOREIGN sleutel en u. tableschema ltgt u. referencedtableschema om hulle te vind vir 'n spesifieke databasis, voeg die WAAR toestand: EN dbname IN (u. tableschema, u. referencedtableschema) Wys Skep sneller MySQL bygevoeg 'n show Skep sneller opdrag in 5.1.21. As jy 'n vroeëre MySQL weergawe gebruik, hier is 'n gestoor proses wat optree soos Wys Skep sneller: DROP PROSEDURE indien bestaan ​​ShowCreateTrigger DELIMITER gaan SKEP PROSEDURE ShowCreateTrigger (IN DB CHAR (64), IN TBL CHAR (64)) Begin SELECT CONCAT (SKEP TRIGGER, triggername, CHAR (10), actiontiming,, eventmanipulation, CHAR (10), OP, eventobjectschema,., eventobjecttable, CHAR (10), VIR ELKE rY, CHAR (10), actionstatement, CHAR (10) Wys Table Status ekwivalent van informationschema Vul skedule en 'n tafel name in. KIES tablename, enjin, weergawe, rowformat, tablerows, avgrowlength, datalength, maxdatalength, indexlength, datafree, autoincrement, createtime, updatetime, checktime, tablecollation, checksum, createoptions, tablecomment uIT informationschema. tafels waar tableschema. eN tablename. Wys tabelle die MySQL SHOW tABELLE opdrag is goed, maar soms wil ons 'n bietjie meer inligting. Hierdie eenvoudige gestoor proses lys die tafel naam, tipe enjin, weergawe, samestelling en rowcount vir elke tafel afsonderlik in 'n databasis. (Individuele databasisse kom en gaan, sodat ons hou al sulke databasis-wye gestoor roetines in 'n databasis.) DROP PROSEDURE indien bestaan ​​showtables SKEP PROSEDURE showtables () Kies tablename AS Table, IFNULL (enjin, sien) enjin, weergawe vanaf weergawe , tablecollation AS n oorsig, tablerows AS rye Ouderdom in jare Gegewe 'n geboortedatum in DOB. hier is twee eenvoudige formules vir ouderdom in jare: Date Format (FromDays (vandag (Curdate ()) - vandag (DOB)), Y) 0 Year (Curdate ()) - Jaar (DOB) - (Reg (Curdate (), 5 ) Dit Reg (DOB, 5)) en hier is een vir ouderdom in jare tot twee desimale plekke, ignoreer dag van maand: Ronde ((((jaar (nou ()) - jaar (DOB))) 12 (((maand (nou ()) - Maand (DOB))))) / 12, 2) Date Time verskil Vind die verskil tussen twee DATETIME waardes in sekondes, minute, ure of dae. As dt1 en dt2 is DATETIME waardes van die vorm jjjj-mm-dd hh: mm: ss, die aantal sekondes tussen dt1 en dt2 is UNIXTIMESTAMP (dt2) - UNIXTIMESTAMP (dt1) Om die aantal minute kloof kry deur 60, vir die aantal ure verdeel 3600, en vir die aantal dae, deel dit deur 3600 24. Duur in jare, maande, dae en tyd drop funksie indien bestaan ​​PeriodLen drop funksie indien bestaan ​​NumLabel DELIMITER kon gaan FUNKSIE PeriodLen (dt1 DATETIME, dt2 DATETIME ) gee terug CHAR (128) Begin verklaar jj, M0, mm, D0, dd, HH, mi, SS, T1 BIGINT vERKLAAR dtmp DATETIME verklaar t0 tijdsaanduiding instel jj TIMESTAMPDIFF (JAAR, dt1, dt2) STEL M0 TIMESTAMPDIFF (maand, dt1, . dt2) STEL mm M0 MOD 12 uiteengesit dtmp ADDDATE (dt1, interval M0 MAAND) STEL D0 TIMESTAMPDIFF (dAG, dt1, dt2) Die datum van aanstaande donderdag Gegewe 'n datum en sy weekdag nommer (1Sunday 7Saturday), is daar drie moontlikhede: 1. Vandag is Donderdag toe volgende Donderdag is 7 dae van nou af. 2. Vandag is voor Donderdag Toe volgende Donderdag is (5 minus vandag weekdag getal) van nou af. 3. Vandag is ná Donderdag Toe volgende Donderdag is 7 (5 minus vandag weekdag nommer). stel dcurdate () stel N dayofweek (curdate ()) kies d: adddate (curdate (), 0) as datum, N: dayofweek (adddate (curdate (), 0)) as weekdag, Track wanneer 'n waarde verander Jy het 'n tafel wat 'n waarde en die tyd wanneer die waarde gemeet spore. drop tafel as bestaan ​​veranderinge skep tafel veranderinge (tyd tyd, waarde int) voeg in veranderinge waardes (00:00, 0), (01:05, 1), (01:09, 1), (01:45, 1) , (02:24, 0), (00:20, 1), (00:40, 0), (14:32, 0), Wat maand maak 'n week val in die maand van 'n week is dubbelsinnig as die week weerskante maande. As ons aanneem die konvensie dat die maand van 'n week is die maand van sy begin Sondag, dan op 29 November 2009. WIT weekno Maand CurDate ()) Stel datum AddDate (2009/01/01, 7weekno) bepaalde dag DayOfWeek (datum) STEL datecomp AS (dag 1, datum, AddDate (datum, 1-dag)) Kies datum, dag, datecomp, maand (datecomp) AS maand ------------------- ------------------ datum dag datecomp maand ---------------------------- --------- 2009/12/03 5 2009/11/29 11 ----------------------------- -------- YearMonth () Ons moet dikwels datetimes gebaseer op jaar en maand bereken. Hierdie klein funksie vereenvoudig sulke navrae: stel globale logbintrustfunctioncreators1 skep funksie yearmonth (d datum) gee terug int terugkeer 100year (d) maand (d) Dan tot op datum waardes vind binne die tydperk van drie maande wat begrens word deur die eerste dag van die afgelope maand en die laaste dag van die volgende maand, skryf. Kies d van TBL waar yearmonth (d) tussen yearmonth (curdate () - interval 1 maand) en yearmonth (curdate () interval 1 maand) Oudit roetes en punt-in-time argitektuur Vind oorvleuelende tydperke Jy het 'n tafel van besoeke en jy wil die tydperke waartydens daar besoek tyd oorvleuel vertoon. drop tafel as bestaan ​​besoeke te skep tafel besoeke (primêre sleutel ID int, begin date time, einde DATETIME) voeg in besoeke waardes (1, 2008/09/01 15:01, 2008/09/01 15:04), (2, 2008 -09-01 15:02, 2008/09/01 15:09), (3, 2008/09/01 15:12, 2008/09/01 15:15), (4, 2008/09/01 16: 11, 2008/09/01 16:23), (5, 2008/09/01 16:19, 2008/09/01 16:25), (6, 2008/09/01 17:52, 2008-09- 01 17:59), (7, 2008/09/01 18:18, 2008/09/01 18:22), (8, 2008/09/01 16:20, 2008/09/01 16:22), Vind opeenvolgende duplikate 'n tabel wat tydperke spore kan tydperk uniekheid vereis. Dit beteken dat dit geen volgorde duplikate. As 'n tafel het kolomme processID, Begindatum en Einddatum, daardie drie kolomme is tydperk unieke indien daar geen denim rye met dieselfde processID en oorvleueling Begindatum en Einddatum waardes bestaan. As daar so 'n paar rye, die tafel uitstallings volgorde duplisering. Nog 'n manier om te sê dit: as 'n oomblik is die kleinste DATETIME eenheid van Begindatum en Einddatum kolomme, dan as daar geen volgorde duplikate, daar is presies een processID waarde op enige tydstip. Hier is 'n navraag om opeenvolgende duplikate vir diegene kolomme vind: KIES t. processid UIT TBL t WAAR bestaan ​​(Kies uit TBL AS T3 WAAR t3.processid is van nul) OR bestaan ​​(Kies uit TBL AS T1 WAAR 1 LT (Kies COUNT (processid ) uIT TBL AS T2 WAAR t1.processid t2.processid eN t1.startdate Dit t2.enddate eN t2.startdate Dit t1.enddate)) binne of buite op 'n gegewe datum en tyd Werknemers punch in en uit. Jy hou hierdie gebeure met. drop tafel as bestaan ​​TBL skep tafel TBL (empno smallint, clockdate datum, clocktime tyd, clocktype kar (1)) voeg in TBL waardes (1, 2014/05/15, 09:00:00, I), (1, 2014 -05-15, 11:00:00, o), (1, 2014/05/15, 00:30:00, I), (1, 2014/05/15, 19:00:00, o) Was werknemer 1 of buite om 12:30 op 15 Mei 2014 gebruik 'n self-sluit om die binne en buite gebeure speel teen die gegewe datum tyd. kies as (tel (1), Ja, Nee) as InStore van TBL n deel TBL b behulp (empno, clockdate) waar empno1 en a. clockdate2014-5-15 en a. clocktimelt12: 30: 00 en a. clocktypeI en b. clocktimegt12: 30: 00 en b. clocktypeO --------- InStore --------- Ja --------- Peak besoek tellings deur DATETIME tydperk Jy het 'n besoek tafel (id int, begin date time, einde DATETIME). en jy wil piek besoek tellings dop. 'N eenvoudige oplossing is om self-sluit op nie-wat ooreenstem met die ID's en oorvleueling besoek tye, groep deur ID, dan bevel deur die gevolglike tel: Kies a. id, groupconcat (b. id) as Oorvleuelings, tel (b. id) 1 as OverlapCount UIT besoeke 'n sluit besoeke b op a. id Dit b. id en a. start Dit b. end en b. start Dit a. end GROUP BY a. id ORDER BY OverlapCount DESC Pivot tafel skedule Jy het 'n skedule tafel ( tydperk, dag, onderwerp, kamer) met 'n primêre sleutel tydperk, dag na duplikaat besprekings vermy. Jy wil die skedule as tydperke, vakke en kamers in rye, en dae van die week in kolomme vertoon. SELECT tydperk, Max (INDIEN (Dag 1, CONCAT (onderwerp,, kamer),)) AS Mon, Max (INDIEN (dag2, CONCAT (onderwerp,, kamer),)) AS Mon, Max (INDIEN (day3, CONCAT (onderhewig ,, kamer),)) AS Mon, Max (INDIEN (dag4, CONCAT (onderwerp,, kamer),)) AS Mon, Max (INDIEN (day5, CONCAT (onderwerp,, kamer),)) AS Mon UIT skedule GROEP DEUR tydperk MAX () kies bestaande oor leeg inskrywings, en GROUP BY lyne alles op dieselfde ry. Wys kolom waardes wat N keer SELECT id van TBL GROEP voorkom DEUR ID WAT COUNT () N Verander die WAT toestand 1 na die lys dubbele waardes, ens Ouers sonder kinders Gegewe tafels ouer (ID INT), kind (ID INT, parentid INT ). hoe vind ons ouers met geen kinders Dit is die All X waarvoor daar geen Y patroon, wat geskryf kan word as 'n negatief te sluit. SELECT parent. id van 'n ouer links JOIN kind parent. id child. parentid WAAR child. parentid is van nul of met 'n NIE BESTAAN subquery, wat is logies ekwivalent aan die uitsluiting aan te sluit, maar gewoonlik verrig veel stadiger: KIES parent. id AS ParentID van 'n ouer wat nie van bestaan ​​(SELECT parent. id van 'n ouer JOIN kind parent. ID child. parentid) partye wat kontrakte met mekaar Jy het 'n party tafel wat meer inligting oor die mense se name ens hou, en het 'n kontrak tafel waar elke ry het KlientID en contractorID waarde dui op 'n parties. partyID waarde - dit is, elke kontrakte ry punte by twee partye rye. Jy wil 'n lys van die name van al die kontrakteurs en hul kliënte. KIES clientpartyID, pCli. name as kliënt, contractorpartyID, pCon. name AS Kontrakteur uit kontrakte INNER JOIN partye AS pCli OP contracts. clientpartyID pCli. partyID INNER JOIN partye AS pCon OP contracts. contractorpartyID pCon. partyID Ouers met en sonder kinders Jy het partye en kontrakte tafels. Elke kontrakte ry het 'n contractorpartyID waarde wat 'n ry verwysings in partye en 'n clientpartyID waarde wat ook verwys na 'n ry in partye. Hoe om 'n lys van al die partye en hul kontrakte, wat spasies as leë stringe eerder as NULLs SELECT parties. partyID, IFNULL (contractorpartyID,) AS kontrakteur, IFNULL (clientpartyID,) as kliënt van die partye links JOIN contractorclient OP partyIDcontractorpartyID ORDER BY partyID --- -------------------------- partyID kontrakteur kliënt --------------------- -------- 1 2 2 1 3 ----------------------------- Pagination Veronderstel jy het 'n telefoon boek van name, adresse, ens jy vertoon 20 rye per bladsy, jy op bladsy 100, en jy wil om bladsy 99. vertoon Hoe doen jy dit weet net watter bladsy jy op Veronderstel. 1-gebaseerde bladsynommers jy op bladsy P elke bladsy toon N rye dan die algemene formule vir die vertaling van 'n 1-gebaseerde bladsynommer in 'n eerste LIMIET argument is. MAX (0, P-1) N wat vir die 99 20-ry bladsy evalueer tot 1960, en die tweede argument te beperk is net N, so na bladsy 99 kyk, skryf. KIES. Perk (1960 N) Die probleem met hierdie is skalering. MySQL nie die geval optimaliseer limiet alles goed. KIES. LIMIET 1000000,20 ongelukkig sal haal nie net die twintig rye vanaf die miljoenste ry dit sal 'n miljoen en twintig rye te haal voordat dit wys dat jy die 20 wat jy gevra Hoe groter die resultaat, hoe langer LIMIET takes. Whats die alternatiewe Bou paginering logika in die WAAR klousule, en te verseker dat daar 'n bedekking indeks vir die paginering kolom. Op 'n tafel van 100,000 geïndekseer ewekansige heelgetalle, te kies. WAAR. vir die afgelope 20 heelgetalle in die tabel is twee keer so vinnig as die vergelykbare LIMIET navraag. Met 'n miljoen heelgetalle, sy meer as 500 keer vinniger as jou koppelvlak verg wat net 20 rye per bladsy op 'n gegewe orde, haal die twintig rye, plus die ry net voor die vasgestelde indien dit bestaan, plus die volgende ry na die twintig indien dit bestaan. Wanneer die gebruiker die Vorige Bladsy knoppie druk, pas die WAAR klousule rye spesifiseer waar die sleutel waarde is bevel deur die huidige indeks DESC LIMIT 20 En toe die gebruiker die Volgende bladsy knoppie druk, het die WAAR klousule spesifiseer kernwaardes wat van die ry net na die stel, en bevel deur die huidige indeks ASC LIMIT 20. Maak waardes van 'n kolom opeenvolgende Jy het 'n tafel TBL met 'n kolom waarvan die waardes kan vrylik oorskryf en wat gevul moet word met perfek opeenvolgende waardes wat begin met 1: SET I0 UPDATE TBL SET keycol (i: i1) Maar meer dikwels, wat wat jy nodig het is 'n meganisme vir die waarborg van 'n ononderbroke reeks heelgetal sleutel waardes wanneer 'n ry is ingevoeg. Hier is die slegte nuus: autoincrement verskaf nie hierdie logika, byvoorbeeld dat dit nie verhoed inserton mislukkings, redigering van die kolom, of daaropvolgende verwydering. Die goeie nuus is dat die logika vir die waarborg van 'n ononderbroke ry is eenvoudig: (ii) 'n tafel met die verlangde volgorde in een kolom en verdere kolom (s) te gebruik (om so 'n tafel vir opeenvolgende kolomme in verskeie ander tafels te spoor , bied 'n dop kolom vir elke sodanige tafel) (iii) skryf Triggers na die volgende beskikbare opeenvolgende sleutel van daardie tafel te gaan haal, merk dit gebruik, en wys dit op Insert (iv) sit al sulke logika binne transaksie blokke (v) verbied verwydering in die tabel. Volg stapsgewyse projek voltooiing n meester tafel het een ry vir elke projek, en die aantal opeenvolgende stappe wat nodig is om elke projek te voltooi. A detail tafel het een ry per projek per voltooide stap: DROP TABLE indien bestaan ​​T1 CREATE TABLE T1 (ID INT, projectname CHAR (2), projectsteps INT) voeg in T1 WAARDES (1, xx, 3), (2, jj, 3), (3, ZZ, 5) dROP TABLE indien bestaan ​​T2 CREATE TABLE T2 (wen strepe Gegewe 'n tafel van ID's en wen-verloor resultate, hoe vind ons die langste segetog druppel tafel as bestaan ​​resultate te skep tafel resultate (id int, gevolg kar (1)) voeg in resultate waardes (1, w), (2, l), (3, l), (4, w), (5, w), (6, w), (7 , l), (8, w), (9, w) te kies uit die resultate -------------- ID gevolg -------------- 1 w 2 l 3 l groot sirkel afstand Vind die afstand in kilometer tussen twee punte op die oppervlak van die aarde. Dit is net die soort van probleem gestoor funksies is gemaak vir. vir 'n eerste orde benadering, ignoreer afwykings van die aarde se oppervlak van die perfek . sferiese Toe die afstand in radiale gegee word deur 'n aantal van trigonometriese formules Inligting en veiligheid en COS tree redelik. COS (lat1-lat2) (1COS (lon1-lon2)) - COS (lat1lat2) (1-COS (lon1-lon2) ) rads Inligting en veiligheid (---------------------------------------------- -----------------------) 2 ons moet grade breedtegraad en lengtegraad te skakel na radiale, en ons moet die lengte in km van een radiaal op die ken aarde se oppervlak, wat 6378,388. Die funksie: stel logbintrustfunctioncreatorsTRUE Moving gemiddelde Gegewe 'n tafel van datums en daaglikse waardes, haal hul bewegende 5-dag gemiddeld: DROP TABLE indien bestaan ​​t CREATE TABLE t (dt DATUM, afname INT) voeg in t WAARDES (2007/01/01, 5), (2007-1-2,6), (2007-1-3,7), (2007-1-4,8), (2007-1-5,9), (2007/01/06, 10), (2007-1-7,11), (2007-1-8,12), (2007-1-9,13) Kies a. dt, a. qty, Round ((kies som (b. qty ) / COUNT (b. qty) van T aS b meerdere somme oor 'n aan te sluit Jy het 'n party tafel wat meer inligting oor die mense se name ens hou, en 'n kontrakte tabel op waarin elke ry definieer een kontrak, die identifisering van 'n kliënt as clientpartyID en 'n kontrakteur as contractorpartyID, elkeen van hierdie 'n vreemde sleutel verwysing parties. partyID. Jy wil 'n lys van partye wat hoeveel kontrakte hulle deelgeneem het as kliënt, en hoeveel theyve deelgeneem as kontrakteur. KIES p. partyID, p. name, ( Kies COUNT () VAN contractorclient c1 WAAR c1.clientpartyID p. partyID) AS ClientDeals, (kies COUNT () VAN contractorclient C2 WAAR c2.contractorpartyID p. partyID) AS ContractorDeals UIT partye p ORDER BY partyID Persentiele In die Sakila tafel film. haal 'n top-down persentiel posisie van film lengtes: KIES a. filmid. ROUND (100.0 (kies COUNT () VAN film as b WAAR b. length Dit a. length) / total. cnt, 1) AS persentiel van die film 'n CROSS JOIN (Kies COUNT () AS tel van die film) AS totaal Sorteer persentiel Latere