SQL Server : Aggregatfunktionen und GROUP BY in langen Anfragen

Ich war das schreiben einer Abfrage, die neulich in SQL Server, und hatte, um eine Summe einer Spalte der Abfrage Hinzugefügt.

Nun die Abfrage, die ich hatte, um die Summe der Spalte schon über 20 ausgewählten Spalten.

In der Reihenfolge für die Summe (Aggregat-Funktion) zu arbeiten, musste ich hinzufügen GROUP BY für je eine ausgewählte Spalte.

Diese endete als eine sehr mühsame und langweilige Aufgabe (siehe die folgende Abfrage). Ich hatte, um rund 50 neue Linien, um die Abfrage nur um die SUMME für eine Spalte zu arbeiten.

Gibt es eine Möglichkeit zur Automatisierung schreiben von Abfragen mithilfe von SQL Server Management Studio?

Ist dies das gleiche mit anderen Datenbanken (PL/SQL, Oracle, MySQL?)

Dies ist, was die Abfrage aussieht.

SELECT 
       mvLogisticContracts.[mvLogisticContract_id]
      ,mvLogisticContracts.[CONTRACTNUMBER] 
      ,mvLogisticContracts.[CUSTNMBR]
      ,mvLogisticContracts.[DateCreated]
      ,mvLogisticContracts.[TruckAllocatedTotal]      ,mvLogisticContracts.[mvLOgisticAddress_id_StartOriginal]
          ,mvLogisticContracts.[mvLogisticAddress_id_StartOriginal]       ,mvLogisticContractLineItems.[LineItemQuantity]     ,mvLogisticContractLineItems.[LineItemNo]
          ,mvLogisticContractLineItems.[OffloadRequired]
          ,mvLogisticContractLineItems.[CarrierRequested]     ,mvLogisticContractLineItems.[RequestedSerialNo]
          ,mvLogisticContractLineItems.[mvLogisticAddress_id_Start]
          ,mvLogisticContractLineItems.[DateReadyBy]      ,mvLogisticContractLineItems.[DateCustomerRequested],
          mvLogisticContractLineItems.[mvLogisticContractLineItem_id]
      ,mvLogisticSalespersons.[FirstName],mvLogisticSalespersons.[LastName],
      mvLogisticServiceTypes.ServiceType,
      mvLogisticStatuses.Description,
      mvLogisticSKUs.[SKU],       mvLogisticSKUs.[Length],
      a1.CITY as \"CityStart\", a1.AddressName as \"AddressNameStart\", a1.Address1 as \"Address1Start\", a1.STATE as \"StateStart\", a1.ZIP as \"ZipStart\",
      a2.CITY as \"CityEnd\", a2.AddressName as \"AddressNameEnd\", a2.Address1 as \"Address1End\", a2.STATE as \"StateEnd\", a2.ZIP as \"ZipEnd\",
      mvLogisticContracts.[mvLogisticAddress_id_Start] AS aa1,
      mvLogisticContracts.[mvLogisticAddress_id_End] AS aa2,
      SUM(mvLogisticReleases.ReleaseQuantity) as ReleaseQtySum
            FROM mvLogisticContractLineItems
            LEFT JOIN mvLogisticContracts ON mvLogisticContractLineItems.mvLogisticContract_id = mvLogisticContracts.mvLogisticContract_id
            LEFT JOIN mvLogisticSalespersons ON mvLogisticContracts.[mvLogisticSalesperson_id] = mvLogisticSalespersons.[mvLogisticSalesperson_id]
            LEFT JOIN mvLogisticServiceTypes ON mvLogisticContractLineItems.mvLogisticServiceType_id = mvLogisticServiceTypes.mvLogisticServiceType_id
            LEFT JOIN mvLogisticStatuses ON mvLogisticContractLineItems.mvLogisticStatus_id = mvLogisticStatuses.mvLogisticStatus_id
            LEFT JOIN mvLogisticSKUs ON mvLogisticContractLineItems.[SKU_id] = mvLogisticSKUs.[SKU_id]
            LEFT JOIN mvLogisticAddresses a1 ON a1.[mvLogisticAddress_id] = mvLogisticContractLineItems.[mvLogisticAddress_id_Start]
            LEFT JOIN mvLogisticAddresses a2 ON a2.[mvLogisticAddress_id] = mvLogisticContractLineItems.[mvLogisticAddress_id_End]
            /*LEFT JOIN mvLogisticAddresses a1 ON a1.[mvLogisticAddress_id] = mvLogisticContracts.[mvLogisticAddress_id_Start]
            LEFT JOIN mvLogisticAddresses a2 ON a2.[mvLogisticAddress_id] = mvLogisticContracts.[mvLogisticAddress_id_End]*/
            LEFT JOIN mvLogisticReleases ON mvLogisticContractLineItems.mvLogisticContractLineItem_id = mvLogisticReleases.mvLogisticContractLineItem_id
            GROUP BY
            mvLogisticContracts.[mvLogisticContract_id]
      ,mvLogisticContracts.[CONTRACTNUMBER] 
      ,mvLogisticContracts.[CUSTNMBR]
      ,mvLogisticContracts.[DateCreated]
      ,mvLogisticContracts.[TruckAllocatedTotal]      ,mvLogisticContracts.[mvLOgisticAddress_id_StartOriginal]
          ,mvLogisticContracts.[mvLogisticAddress_id_StartOriginal]       ,mvLogisticContractLineItems.[LineItemQuantity]     ,mvLogisticContractLineItems.[LineItemNo]
          ,mvLogisticContractLineItems.[OffloadRequired]
          ,mvLogisticContractLineItems.[CarrierRequested]     ,mvLogisticContractLineItems.[RequestedSerialNo]
          ,mvLogisticContractLineItems.[mvLogisticAddress_id_Start]
          ,mvLogisticContractLineItems.[DateReadyBy]      ,mvLogisticContractLineItems.[DateCustomerRequested],
          mvLogisticContractLineItems.[mvLogisticContractLineItem_id]
      ,mvLogisticSalespersons.[FirstName],mvLogisticSalespersons.[LastName],
      mvLogisticServiceTypes.ServiceType,
      mvLogisticStatuses.Description, 
      mvLogisticSKUs.[SKU],       
      mvLogisticSKUs.[Length], 
      a1.CITY, a1.AddressName, a1.Address1, a1.STATE, a1.ZIP, 
      a2.CITY, a2.AddressName, a2.Address1, a2.STATE, a2.ZIP, 
      mvLogisticContracts.[mvLogisticAddress_id_Start], 
      mvLogisticContracts.[mvLogisticAddress_id_End]

InformationsquelleAutor Tool | 2012-09-18

Unterabfragen und common table expressions (CTEs) sind zwei Arten zu isolieren, die aggregation(s) bis zu einem festgelegten Ort, der leicht an der Haupt-SELECT-Anweisung.

Korreliert/synchronisierte Unterabfrage Beispiel:

SELECT c.contract_id, c.contractnum, c.customernum,
    ( SELECT SUM( li.price * li.orderquantity ) 
     FROM lineitems li 
     WHERE li.contract_id = c.contract_id
     ) AS grand_total
FROM contracts c 
WHERE ...

Verweisen auf die korrelierte Unterabfrage in der SELECT-Liste ist ein bequemer Weg, um wieder einen einzelnen Wert.

Beitritt zu einer CTE:

WITH ordersummary AS 
( SELECT li.contract_id, 
      COUNT( DISTINCT li.partnum ) AS distinctproducts, 
      SUM( li.orderquantity ) AS itemcount,
      SUM( li.price * li.orderquantity) AS totalprice,
      MIN( p.ship_ready_date ) AS earliest_partial_ship_date,
      MAX( p.ship_ready_date ) AS earliest_complete_ship_date
      FROM lineitems li 
      INNER JOIN parts p 
          ON p.partnum = li.partnum
      GROUP BY li.contract_id
)
SELECT c.contract_id, c.contractnum, c.customernum,
s.distinctproducts, s.itemcount, s.totalprice,
s.earliest_partial_ship_date, s.earliest_complete_ship_date
FROM contracts c 
INNER JOIN ordersummary s
ON s.contract_id = c.contract_id
WHERE ...

Beitritt zum Ausdruck:

SELECT c.contract_id, c.contractnum, c.customernum,
s.distinctproducts, s.itemcount, s.totalprice,
s.earliest_partial_ship_date, s.earliest_complete_ship_date
FROM contracts c 
INNER JOIN
( SELECT li.contract_id, 
      COUNT( DISTINCT li.partnum ) AS distinctproducts, 
      SUM( li.orderquantity ) AS itemcount,
      SUM( li.price * li.orderquantity) AS totalprice,
      MIN( p.ship_ready_date ) AS earliest_partial_ship_date,
      MAX( p.ship_ready_date ) AS earliest_complete_ship_date
      FROM lineitems li 
      INNER JOIN parts p 
          ON p.partnum = li.partnum
      GROUP BY li.contract_id
) AS s
ON s.contract_id = c.contract_id
WHERE ...

Edit: ich glaube ich habe gefunden was ich Suche (mithilfe von Unterabfragen, die du erwähnt hast); pastebin.com/LjWyCgak. Vielen Dank.
Ich habe einige Beispiele für die Verwendung von table expressions, mit denen Sie abrufen, mehrere Spalten auf einmal.
Wenn dies Ihre Frage beantwortet markieren Sie es als "Akzeptiert" damit andere daraus lernen können auch.

InformationsquelleAutor Fred Sobotka

1

Habe ich zwei Vorschläge

1) Fügen Sie eine Sicht auf die Datenbank. Diese Ansicht ist aus einem "berechnete" Spalte und den anderen Spalten, die Sie benötigen. Die berechnete Spalte ist das Ergebnis der Verkettung der Spalten zusammen aus einer Abfrage mit all Ihren Verknüpfungen. Zum Beispiel "SELECT CONCAT(col1,col2), col3 FROM x JOIN y ... z ..."

Können Sie dies eine materialisierte Ansicht, die eine bessere Leistung, da seine auf der Diskette abgespeichert. Und damit Sie vermeiden Beitritt on-the-fly.

2) Versuchen Sie, und vermeiden Sie die Gruppierung, indem Sie alle Felder in den ersten Platz. Zum Beispiel finden Sie heraus, was ist einzigartig für jede Zeile, die Sie brauchen, die Summe für. Vielleicht brauchen Sie nicht, um alle diese Felder. Und in dem Fall, dass Sie tun, können Sie beginnen, Sie zu gruppieren und später join mit den Tabellen zur Rückgabe der Spalten, die Sie benötigen.

EDIT: ich Stimme mit Fred Sobotka, Unterabfragen ist ein guter Weg zu gehen. Aber es hängt davon ab, was deine Daten Aussehen. Im Allgemeinen sollten Sie versuchen und begrenzen Sie die Anzahl der joins, vor allem, wenn Sie haben ein großes dataset. Schließt sich neigen, langsam zu sein.

Ich denke, ich löste es, indem Sie die Abfrage: pastebin.com/m2ArmxGc

InformationsquelleAutor jonathan

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.