Manghenelo  Mintirho ya fasitere i xivumbeko xa matimba eka SQL lexi tirhisiwaka ku endla swibalo ku tsemakanya sete ya tilayini leti fambelanaka na layini ya sweswi. Ku hambana na mintirho ya nhlengeleto, mintirho ya mafasitere a yi hlengeleti tilayini eka vuhumelerisi byin’we; va vuyisa mbuyelo wa layini yin’wana na yin’wana loko va ri karhi va hlayisa mongo wa dataset.  Eka xihloko lexi, hi ta kambisisa mintirho yin’wana ya fasitere ra SQL leyi tirhisiwaka ngopfu (   ,   ,   ,   ,   , na   ) hi swikombiso. ROW_NUMBER() RANK() DENSE_RANK() NTILE() LEAD() LAG()  Tafula ra Xikombiso: Data ya ku Xavisa  Hi ta tirhisa tafula leri landzelaka ra ku Xavisa ku kombisa mintirho ya fasitere:  ID ya ku xavisa  ID ya Muxavi  Ximakiwa  Ndhawu  Ntsengo  Siku ro Xavisiwa  1. 1.  101. 101  Laptop  N'walungu  1200  2023-01-05  2. 2.  102. 102  Xiphepherhele  N'walungu  800  2023-02-15  3. 3.  103. 103  Riqingho  N'walungu  800  2023-03-10  4. 4.  104. 104  Xiphepherhele  N'walungu  500  2023-04-01  5.  105. 105  Laptop  Dzonga  1300  2023-05-05  6. 6.  106. 106  Xiphepherhele  Dzonga  700  2023-06-20  7. 7.  107. 107  Riqingho  Vupela-dyambu  900  2023-07-15  8. 8.  108. 108  Laptop  Vuxeni  1300  2023-08-10  1. NOMBORO_RA_RA_RAYI() .  Ntirho wa ROW_NUMBER() wu avela nomboro yo hlawuleka eka layini yin’wana na yin’wana endzeni ka xiphemu, leyi odariweke hi kholomo leyi boxiweke.    : Nyika nomboro ya layini yo hlawuleka eka ku xavisiwa kun’wana na kun’wana endzeni ka xifundzankulu hi ku ya hi ntsengo wa ku xavisiwa (ehenhla ku ya eka ya le hansi). Ntirho   SELECT SalesID, Region, Amount, ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Amount DESC) AS RowNum FROM Sales;    : Mbuyelo  ID ya ku xavisa  Ndhawu  Ntsengo  Nomboro ya Nxaxamelo  1. 1.  N'walungu  1200  1. 1.  2. 2.  N'walungu  800  2. 2.  3. 3.  N'walungu  800  3. 3.  4. 4.  N'walungu  500  4. 4.  5.  Dzonga  1300  1. 1.  6. 6.  Dzonga  700  2. 2.  7. 7.  Vupela-dyambu  900  1. 1.  8. 8.  Vuxeni  1300  1. 1.  2. XIYIMO XA LE HENHLA() .  Ntirho wa RANK() wu avela xiyimo eka layini yin’wana na yin’wana endzeni ka xiphemu. Tilayini leti nga na mimpimo leyi fanaka ti kuma xiyimo lexi fanaka, naswona xiyimo lexi landzelaka xa tluriwa.    : Ku veka ku xavisiwa endzeni ka xifundzha xin’wana na xin’wana hi mali (ya le henhla ku ya eka ya le hansi). Ntirho   SELECT SalesID, Region, Amount, RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS Rank FROM Sales;    : Mbuyelo  ID ya ku xavisa  Ndhawu  Ntsengo  Xiyimo xa le henhla  1. 1.  N'walungu  1200  1. 1.  2. 2.  N'walungu  800  2. 2.  3. 3.  N'walungu  800  2. 2.  4. 4.  N'walungu  500  4. 4.  5.  Dzonga  1300  1. 1.  6. 6.  Dzonga  700  2. 2.  7. 7.  Vupela-dyambu  900  1. 1.  8. 8.  Vuxeni  1300  1. 1.    : . Xivumbeko xa Nkoka  Eka xifundzha xa N’walungu, havumbirhi bya Ntsengo = 800 wa tilayini ti avelana xiyimo xa 2.  Xiyimo lexi landzelaka xi tluriwa (i.e., xiyimo xa 3 xi kayivela) ivi xi tlula xi ya eka 4.  3. XITLHANYI_XA_DENSE () .  Ntirho wa DENSE_RANK() wu avela ti rank ku fana na RANK(), kambe a wu tluli ti rank endzhaku ka ti ties.    : Avela ti dense ranks eka ku xavisiwa endzeni ka xifundzha xin’wana na xin’wana hi mali (ya le henhla kuya eka yale hansi). Ntirho   SELECT SalesID, Region, Amount, DENSE_RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS DenseRank FROM Sales;    : Mbuyelo  ID ya ku xavisa  Ndhawu  Ntsengo  DenseRank ya xirhendzevutani  1. 1.  N'walungu  1200  1. 1.  2. 2.  N'walungu  800  2. 2.  3. 3.  N'walungu  800  2. 2.  4. 4.  N'walungu  500  3. 3.  5.  Dzonga  1300  1. 1.  6. 6.  Dzonga  700  2. 2.  7. 7.  Vupela-dyambu  900  1. 1.  8. 8.  Vuxeni  1300  1. 1.    : . Xivumbeko xa Nkoka  Eka xifundzha xa N’walungu, havumbirhi bya Ntsengo = 800 wa tilayini ti avelana xiyimo xa 2.  Xirhendzevutani lexi landzelaka i 3, ku nga ri na ku tlula ka swiyimo.  4. NTILE() .  NTILE() yi avanyisa tilayini hi nhlayo leyi vekiweke ya mintlawa leyi nga kwalomu ka ku ringana.    : Avanyisa ku xavisiwa hinkwako hi mintlawa ya 4 hi ku ya hi Nhlayo hi ku landzelelana loku yaka ehansi. Ntirho   SELECT SalesID, Amount, NTILE(4) OVER (ORDER BY Amount DESC) AS Quartile FROM Sales;    : Mbuyelo  ID ya ku xavisa  Ntsengo  Quartile ya xirhendzevutani  5.  1300  1. 1.  8. 8.  1300  1. 1.  1. 1.  1200  2. 2.  7. 7.  900  2. 2.  2. 2.  800  3. 3.  3. 3.  800  3. 3.  4. 4.  500  4. 4.  6. 6.  700  4. 4.  5. XITLHOKOVETSELO() .  LEAD() yi vuyisa nkoka eka layini leyi landzelaka endzeni ka xiphemu lexi fanaka.    : Fanisa ntsengo wun’wana na wun’wana wo xavisa na ntsengo lowu landzelaka wo xavisa, lowu odariweke hi SaleDate. Ntirho   SELECT SalesID, Amount, LEAD(Amount) OVER (ORDER BY SaleDate) AS NextAmount FROM Sales;    : Mbuyelo  ID ya ku xavisa  Ntsengo  Ntsengo lowu landzelaka  1. 1.  1200  800  2. 2.  800  800  3. 3.  800  500  4. 4.  500  1300  5.  1300  700  6. 6.  700  900  7. 7.  900  1300  8. 8.  1300  HAVA  6. LAG() .    yi vuyisa nkoka eka layini leyi hundzeke endzeni ka xiphemu lexi fanaka. LAG()    : Fanisa mali yin’wana na yin’wana yo xavisa na mali yo xavisa ya khale, leyi odaweke hi SaleDate. Ntirho   SELECT SalesID, Amount, LAG(Amount) OVER (ORDER BY SaleDate) AS PrevAmount FROM Sales;    : Mbuyelo  ID ya ku xavisa  Ntsengo  PrevNhlayo ya le mahlweni  1. 1.  1200  HAVA  2. 2.  800  1200  3. 3.  800  800  4. 4.  500  800  5.  1300  500  6. 6.  700  1300  7. 7.  900  700  8. 8.  1300  900  Mahetelelo  Mintirho ya fasitere ra SQL yo fana na ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LEAD(), na LAG() yi nyika tindlela ta matimba to xopaxopa datha endzeni ka swiphemu.   Swilo Swa Nkoka Swa Ku Tekiwa:    yi avela xihlawulekisi xo hlawuleka eka layini yin’wana na yin’wana. ROW_NUMBER()    na   swi hambana hi ndlela leyi swi khomaka vuxaka ha yona (ku tlula vs. ku pfumala ku tlula). RANK() DENSE_RANK()    ya pfuna ku avanyisa tilayini hi mintlawa ya tinhlayo. NTILE()    na   swi pfumelela ku pimanisiwa na tilayini leti nga ekusuhi. LEAD() LAG()  Hi ku va ni vutshila eka mintirho leyi, u nga khoma mintirho leyi rharhanganeke ya vuxopaxopi ni ya ku veka swiyimo hi ndlela leyinene!  Ndza khensa ku tinyika nkarhi wo lavisisa vutivi lebyi fambelanaka na data na mina. Ndzi tlangela ku tiboha ka n’wina. Loko u kuma rungula leri ri pfuna, ndza ku rhamba leswaku u ndzi landzelela kumbe u tihlanganisa na mina eka   . Happy ku hlota!👋 LinkedIn

Read My Stories

Power BI: Two ways to Union Tables - DAX and Power Query

Audio leyi yi humesiwile hi ririmi ro sungula ra xitori!

Mintirho ya Fasitere ra SQL: Ndzavisiso wa Xihatla Na Swikombiso

About Author

SWIVUTISO SWA SWIVUTISO

HANG TAGS

XITLHOKOVETSELO LEXI XI NYIKERIWE EKA

Related Stories

Digg Free Gear? – A Thank You Contest by HackerNoon

Digg Free Gear? – A Thank You Contest by HackerNoon

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps