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 ( ROW_NUMBER() , RANK() , DENSE_RANK() , NTILE() , LEAD() , na LAG() ) hi swikombiso.

Tafula ra Xikombiso: Data ya ku Xavisa

Hi ta tirhisa tafula leri landzelaka ra ku Xavisa ku kombisa mintirho ya fasitere:

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.

Ntirho : 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).

 SELECT SalesID, Region, Amount, ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Amount DESC) AS RowNum FROM Sales;

Mbuyelo :

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.

Ntirho : Ku veka ku xavisiwa endzeni ka xifundzha xin’wana na xin’wana hi mali (ya le henhla ku ya eka ya le hansi).

 SELECT SalesID, Region, Amount, RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS Rank FROM Sales;

Mbuyelo :

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.

Ntirho : Avela ti dense ranks eka ku xavisiwa endzeni ka xifundzha xin’wana na xin’wana hi mali (ya le henhla kuya eka yale hansi).

 SELECT SalesID, Region, Amount, DENSE_RANK() OVER (PARTITION BY Region ORDER BY Amount DESC) AS DenseRank FROM Sales;

Mbuyelo :

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.

Ntirho : Avanyisa ku xavisiwa hinkwako hi mintlawa ya 4 hi ku ya hi Nhlayo hi ku landzelelana loku yaka ehansi.

 SELECT SalesID, Amount, NTILE(4) OVER (ORDER BY Amount DESC) AS Quartile FROM Sales;

Mbuyelo :

5. XITLHOKOVETSELO() .

LEAD() yi vuyisa nkoka eka layini leyi landzelaka endzeni ka xiphemu lexi fanaka.

Ntirho : Fanisa ntsengo wun’wana na wun’wana wo xavisa na ntsengo lowu landzelaka wo xavisa, lowu odariweke hi SaleDate.

 SELECT SalesID, Amount, LEAD(Amount) OVER (ORDER BY SaleDate) AS NextAmount FROM Sales;

Mbuyelo :

6. LAG() .

LAG() yi vuyisa nkoka eka layini leyi hundzeke endzeni ka xiphemu lexi fanaka.

Ntirho : Fanisa mali yin’wana na yin’wana yo xavisa na mali yo xavisa ya khale, leyi odaweke hi SaleDate.

 SELECT SalesID, Amount, LAG(Amount) OVER (ORDER BY SaleDate) AS PrevAmount FROM Sales;

Mbuyelo :

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:

• ROW_NUMBER() yi avela xihlawulekisi xo hlawuleka eka layini yin’wana na yin’wana.
• RANK() na DENSE_RANK() swi hambana hi ndlela leyi swi khomaka vuxaka ha yona (ku tlula vs. ku pfumala ku tlula).
• NTILE() ya pfuna ku avanyisa tilayini hi mintlawa ya tinhlayo.
• LEAD() na LAG() swi pfumelela ku pimanisiwa na tilayini leti nga ekusuhi.

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 LinkedIn . Happy ku hlota!👋