Re: ROLLUP si CUBE partial in SQL Server 2005

rsocol — Sat, 02 Jun 2007 06:58:01 GMT

Nu sunt familiar cu clauza ROLLUP din Oracle, dar cred că poţi să obţii rezultatul dorit folosind funcţia GROUPING pentru a filtra rândurile cu subtotaluri, de exemplu:

select continent As Continet, countryregioncode As Cod, name as Tara,sum(saleslastyear) As Vanzari
from SalesTerritory group by name, continent,countryregioncode with rollup
HAVING GROUPING(name) = GROUPING(continent)

Folosind un alt exemplu din AdventureWorks, să presupunem că avem următorul view:

CREATE VIEW Purchasing.PurchasesByVendorsAndStates
AS
SELECT V.Name AS VendorName, A.City, s.Name AS StateProvinceName, CountryRegionCode, P.TotalDue
FROM Purchasing.PurchaseOrderHeader P INNER JOIN Purchasing.Vendor V ON P.VendorID = V.VendorID
INNER JOIN Purchasing.VendorAddress VA ON V.VendorID = VA.VendorID
INNER JOIN Person.Address AS A ON VA.AddressID=A.AddressID
INNER JOIN Person.StateProvince S ON A.StateProvinceID=S.StateProvinceID

Dacă folosim direct ROLLUP, astfel:

SELECT VendorName, City, StateProvinceName, CountryRegionCode, SUM(TotalDue) AS TotalDue
FROM Purchasing.PurchasesByVendorsAndStates
GROUP BY CountryRegionCode, StateProvinceName, City, VendorName WITH ROLLUP

Atunci obţinem un rezultat de genul:

VendorName                           City             StateProvinceName   CountryRegionCode TotalDue
------------------------------------ ---------------- ------------------- ----------------- ---------------------
Greenwood Athletic Company           Lemon Grove      Arizona             US                2472770.0523
NULL                                 Lemon Grove      Arizona             US                2472770.0523
NULL                                 NULL             Arizona             US                2472770.0523
Allenson Cycles                      Altadena         California          US                498589.5915
Gardner Touring Cycles               Altadena         California          US                25633.6353
NULL                                 Altadena         California          US                524223.2268
Cruger Bike Company                  Berkeley         California          US                31499.8141
Trikes, Inc.                         Berkeley         California          US                1461653.945
NULL                                 Berkeley         California          US                1493153.7591
[...]
American Bicycles and Wheels         West Covina      California          US                9641.0145
Bloomington Multisport               West Covina      California          US                8243.9511
International Bicycles               West Covina      California          US                1589172.9591
International Trek Center            West Covina      California          US                24070.75
NULL                                 West Covina      California          US                1631128.6747
NULL                                 NULL             California          US                31564099.5102
[...]
Team Athletic Co.                    Spokane          Washington          US                436401.90
Varsity Sport Co.                    Spokane          Washington          US                52176.445
NULL                                 Spokane          Washington          US                488578.345
Electronic Bike Repair & Supplies    Tacoma           Washington          US                2154773.3718
NULL                                 Tacoma           Washington          US                2154773.3718
NULL                                 NULL             Washington          US                18899318.6589
NULL                                 NULL             NULL                US                70479332.6383
NULL                                 NULL             NULL                NULL              70479332.6383
(169 row(s) affected)

Dacă folosim următorul query:

atunci obţinem un rezultat de tipul următor:

VendorName                          City             StateProvinceName  CountryRegionCode TotalDue
----------------------------------- ---------------- ------------------ ----------------- ---------------------
Greenwood Athletic Company          Lemon Grove      Arizona            US                2472770.0523
NULL                                NULL             Arizona            US                2472770.0523
Allenson Cycles                     Altadena         California         US                498589.5915
Gardner Touring Cycles              Altadena         California         US                25633.6353
Cruger Bike Company                 Berkeley         California         US                31499.8141
Trikes, Inc.                        Berkeley         California         US                1461653.945
[...]
American Bicycles and Wheels        West Covina      California         US                9641.0145
Bloomington Multisport              West Covina      California         US                8243.9511
International Bicycles              West Covina      California         US                1589172.9591
International Trek Center           West Covina      California         US                24070.75
NULL                                NULL             California         US                31564099.5102
[...]
Team Athletic Co.                   Spokane          Washington         US                436401.90
Varsity Sport Co.                   Spokane          Washington         US                52176.445
Electronic Bike Repair & Supplies   Tacoma           Washington         US                2154773.3718
NULL                                NULL             Washington         US                18899318.6589
NULL                                NULL             NULL               US                70479332.6383
NULL                                NULL             NULL               NULL              70479332.6383
(107 row(s) affected)

fiind eliminate astfel subtotalurile pe fiecare oraş, păstrând doar subtotalurile după stat, ţară şi totalul general.

Răzvan

ROLLUP si CUBE partial in SQL Server 2005

Stefan — Fri, 01 Jun 2007 16:53:52 GMT

Se poate face o interogare rollup sau cube partial, ca in Oracle?

"select continent As Continet, countryregioncode As Cod, name as Tara,sum(saleslastyear) As Vanzari
from SalesTerritory
group by name rollup (continent,countryregioncode) "

Daca da, cum?

Business Intelligence - OLAP

Re: ROLLUP si CUBE partial in SQL Server 2005

ROLLUP si CUBE partial in SQL Server 2005