piektdiena, 2010. gada 17. septembris

CLR agregātfunkcijas piemērs

Sākot ar SQL Server 2005 ir iespēja veidot agregātfunkcijas izmantojot Visual Studio .NET. Tas nozīmē, ka papildus jau iebūvētām agregātfunkcijām (Sum, Average, Min, Max u.c.), ir iespēja pievienot arī paša veidotas agregātfunkcijas.

Šajā rakstā piemērs, kā izveidot funkciji, kas sarindo vairākus teksta apgabalus vienu aiz otra. Piemēram, ja vienā rindā ir ‘Jānis’ otrā rindā ir ‘Linda’, tad agregātfunkcijas izpildes rezultātā šie abi vārdi tiek savienoti vienā virknē izveidojot teksta virkni ‘Jānis;Linda’.

Piemērā redzams, kā tabulas datus sakārto divās grupās- atkarībā no tā vai KrasaID ir pāra vai nepāra skaitlis. Uzmanība jāpievērš agregāt funkcijai dbo.Concatenate.


Šī agregāt funkcija man ir noderējusi vairākas reizes :). Un gadījumā ja nav skaidrs, ko ar šo kodu iesākt- var paskatīties rakstiņu- CLR 'nekonedara' procedūras izveide un ļoti līdzīgā veidā izveidot jau pavisam noderīgu agregātfunkciju.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.IO;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,
    IsInvariantToNulls = true,
    IsInvariantToDuplicates = true,
    IsInvariantToOrder = true,
    MaxByteSize = 8000)]
public class Concatenate : IBinarySerialize
{
    private StringBuilder sb;

    public void Init()
    {
        sb = new StringBuilder();
    }

    public void Accumulate(SqlString Value)
    {
        if (Value.IsNull)
            return;
        sb.Append(Value.Value.Trim()).Append(";");
    }

    public void Merge(Concatenate Group)
    {
        sb.Append(Group.sb);
    }

    public SqlString Terminate()
    {
        string output = string.Empty;
        if (sb != null && sb.Length > 0)
            output = sb.ToString(0, sb.Length - 1);
        return new SqlString(output);
    }
    public void Read(BinaryReader r)
    {
        sb = new StringBuilder(r.ReadString());
    }
    public void Write(BinaryWriter w)
    {
        w.Write(sb.ToString());
    }
}

Tas arī ir viss, kas nepieciešams, lai realizētu šādu agregātfunkciju. Kā alternatīvu šim risinājumam var izmantot kursoru, tomēr kursora izmantošana rada zobu sāpes, pie tam šādā veidā agregātfunckija nodrošina lielāku ātrdarbību..

Starp citu- kad procedūra ir izveidota uz servera, to var uz citu serveri pārnes tīri ar skripta palīdzību (procedūra noskriptējas nullītēs un vieniniekos). Vienīgi par CLR izpildes atļaušanu neaizmirst (atkal var uzmest aci [pašas beigas rakstam] CLR 'nekonedara' procedūras izveide  un ņemt vērā to, ka CLR koda izpildes atļaušana potenciāli ir drauds drošībai.. lai gan.. kas tad nav).

3 komentāri:

  1. Piemērs labs, bet nav jau obligāti jāizmanto cursor vai CLR. Es darītu šādi:

    WITH Colors
    AS (
    SELECT
    CASE WHEN KrasaID % 2 = '0' THEN 'P' ELSE 'N' END AS PN,
    Krasa AS Krasa
    FROM dbo.Krasas
    )
    SELECT C1.PN,STUFF((
    SELECT ';'+ krasa
    FROM Colors C2
    WHERE C2.PN = C1.PN
    FOR XML PATH('')
    ),1,1,'') Uzskaitijums
    FROM Colors C1
    GROUP BY PN

    AtbildētDzēst
    Atbildes
    1. Šeit citi varianti ko esmu mēģinājis:
      http://www.sqlblog.lv/2011/03/teksta-savirknesana.html
      Man izskatās, ka CLR teksta savirknēšanai strādā visātrāk..

      Dzēst
    2. Mjā, vēl to rakstu var papildināt ar rekursīvo CTE, tik nez kā tur ar performanci :)

      WITH Colors
      AS (SELECT
      CASE WHEN KrasaID % 2 = '0' THEN 'P' ELSE 'N' END AS PN,
      Krasa AS Krasa
      FROM dbo.Krasas),

      Ranked
      AS (SELECT PN,ROW_NUMBER() OVER (PARTITION BY PN ORDER BY PN) AS Nr,
      CAST(Krasa AS VARCHAR(8000)) AS Krasa
      FROM Colors),

      Anchor
      AS (SELECT PN,Nr,Krasa
      FROM Ranked
      WHERE Nr = 1),

      Recursive
      AS (SELECT PN,Nr,Krasa
      FROM Anchor
      UNION ALL
      SELECT Ranked.PN,Ranked.Nr,Recursive.Krasa + ', ' + Ranked.Krasa
      FROM Ranked
      INNER JOIN Recursive
      ON Ranked.PN = Recursive.PN
      AND Ranked.Nr = Recursive.Nr + 1)

      SELECT PN,MAX(Krasa) Uzskaitijums
      FROM Recursive
      GROUP BY PN;

      Dzēst