查询为每个组

Question

我需要在List Price查询的最大Sold Price每个Area，Building Type每Month在标价最高soldprice的百分比。

例如，我们有一个房地产，我们想找到：

在“区”，每卖出Month什么样的Building Type的Sold Price的最大和百分比是多少？

MLS No. Area List Price Contract Price Sold Date Sold Building Type 
N1959472 N11 329500  20/09/2010 317000  13/11/2010 Semi-Detac 
N1990464 N11 339000  08/11/2010 340000  17/11/2010 Apt 

Answer 1

目前尚不清楚'百分比'是什么意思，但我会告诉你如何按月分组以获得最高价格。百分比也可能是一个集结，你可以稍后添加。

select 
    Area, 
    HouseType, 
    max(PriceSold) as MaxPriceSold 
from 
    YourTable 
group by 
    Area, 
    HouseType, 
    Year(DateSold), 
    Month(DateSold) 

Answer 2

这是我最好的猜测，你需要什么。我认为它至少能完成你所要求的。从我得到的，你想按地区，然后建立类型。在每个组中，您需要建筑类型每月销售的最大金额以及销售价格的百分比。 “据我所知，”出售价格的百分比“是完全不明确的（除非我错过了某些东西），所以我认为这是指某个特定地区的特定建筑类型在该月份为该组销售的百分比。如果这些假设的任何一部分都是错误的，就这样说。以下是我得到的结果：

DECLARE @RealEstate TABLE 
(
    MLSNo CHAR(8) 
    , Area CHAR(3) 
    , ListPrice INT 
    , [Contract] DATE 
    , PriceSold INT 
    , DateSold DATE 
    , BuildingType VARCHAR(50) 
); 

INSERT @RealEstate 
VALUES ('N1959472', 'N11', 329500, '20100920', 329500, '20100920', 'Semi-Detac') 
, ('N1990464', 'N11', 339000, '20101108', 339000, '20101108', 'Apt') 
, ('N1990465', 'N11', 331350, '20101124', 331350, '20101124', 'Apt') 
, ('N1990465', 'N11', 359840, '20100313', 359840, '20100313', 'Detac') 
, ('N1990465', 'N11', 351230, '20100320', 351230, '20100320', 'Other Building') 
, ('N1990468', 'N11', 376590, '20100328', 376590, '20100328', 'Outhouse') 
, ('N1990468', 'N11', 374838, '20100315', 374838, '20100315', 'Detac') 
, ('N1990469', 'N11', 351976, '20100328', 351976, '20100328', 'Detac') 
, ('N1990470', 'N11', 348901, '20100303', 348901, '20100303', 'Other Building') 
, ('N1990471', 'N11', 101685, '20100516', 101685, '20100516', 'Other Building') 
, ('N1990472', 'N11', 359875, '20100909', 359875, '20100909', 'Apt'); 


DECLARE @Year INT = 2010; 

WITH Aggregated AS 
(
    SELECT 
     Area 
     , BuildingType 
     , DATENAME(MONTH, DateSold) AS NameOfMonth 
     , PriceSold 
     --, MAX(PriceSold) OVER(PARTITION BY Area, BuildingType, DATENAME(MONTH, DateSold)) AS MaxForMonth 
     , CAST((CAST(PriceSold AS DECIMAL)/(SUM(PriceSold) OVER(PARTITION BY Area, DATENAME(MONTH, DateSold))) * 100) AS INT) AS PercentageOfPriceSold 
    FROM @RealEstate 
    WHERE YEAR(DateSold) = @Year 
) 
, PivotedMax AS 
(
    SELECT 
     Area 
     , BuildingType 
     , ISNULL(January, 0) AS MaxSoldInJanuary 
     , ISNULL(February, 0) AS MaxSoldInFebruary 
     , ISNULL(March, 0) AS MaxSoldInMarch 
     , ISNULL(April, 0) AS MaxSoldInApril 
     , ISNULL(May, 0) AS MaxSoldInMay 
     , ISNULL(June, 0) AS MaxSoldInJune 
     , ISNULL(July, 0) AS MaxSoldInJuly 
     , ISNULL(August, 0) AS MaxSoldInAugust 
     , ISNULL(September, 0) AS MaxSoldInSeptember 
     , ISNULL(October, 0) AS MaxSoldInOctober 
     , ISNULL(November, 0) AS MaxSoldInNovember 
     , ISNULL(December, 0) AS MaxSoldInDecember 
    FROM Aggregated 
    PIVOT 
    (
     MAX(PriceSold) 
     FOR NameOfMonth IN 
     (
      [January] 
      , [February] 
      , [March] 
      , [April] 
      , [May] 
      , [June] 
      , [July] 
      , [August] 
      , [September] 
      , [October] 
      , [November] 
      , [December] 
     ) 
    ) Pivoted 
) 
, PivotedPercent AS 
(
    SELECT 
     Area 
     , BuildingType 
     , ISNULL(January, 0) AS PercentageOfSalesInJanuary 
     , ISNULL(February, 0) AS PercentageOfSalesInFebruary 
     , ISNULL(March, 0) AS PercentageOfSalesInMarch 
     , ISNULL(April, 0) AS PercentageOfSalesInApril 
     , ISNULL(May, 0) AS PercentageOfSalesInMay 
     , ISNULL(June, 0) AS PercentageOfSalesInJune 
     , ISNULL(July, 0) AS PercentageOfSalesInJuly 
     , ISNULL(August, 0) AS PercentageOfSalesInAugust 
     , ISNULL(September, 0) AS PercentageOfSalesInSeptember 
     , ISNULL(October, 0) AS PercentageOfSalesInOctober 
     , ISNULL(November, 0) AS PercentageOfSalesInNovember 
     , ISNULL(December, 0) AS PercentageOfSalesInDecember 
    FROM Aggregated 
    PIVOT 
    (
     MAX(PercentageOfPriceSold) 
     FOR NameOfMonth IN 
     (
      [January] 
      , [February] 
      , [March] 
      , [April] 
      , [May] 
      , [June] 
      , [July] 
      , [August] 
      , [September] 
      , [October] 
      , [November] 
      , [December] 
     ) 
    ) Pivoted 
) 
SELECT 
    PivotedMax.Area 
    , PivotedPercent.BuildingType 
    , PivotedMax.MaxSoldInJanuary 
    , PivotedPercent.PercentageOfSalesInJanuary 
    , PivotedMax.MaxSoldInFebruary 
    , PivotedPercent.PercentageOfSalesInFebruary 
    , PivotedMax.MaxSoldInJanuary 
    , PivotedPercent.PercentageOfSalesInMarch 
    , PivotedMax.MaxSoldInMarch 
    , PivotedPercent.PercentageOfSalesInApril 
    , PivotedMax.MaxSoldInApril 
    , PivotedPercent.PercentageOfSalesInMay 
    , PivotedMax.MaxSoldInMay 
    , PivotedPercent.PercentageOfSalesInJune 
    , PivotedMax.MaxSoldInJune 
    , PivotedPercent.PercentageOfSalesInAugust 
    , PivotedMax.MaxSoldInAugust 
    , PivotedPercent.PercentageOfSalesInSeptember 
    , PivotedMax.MaxSoldInSeptember 
    , PivotedPercent.PercentageOfSalesInOctober 
    , PivotedMax.MaxSoldInOctober 
    , PivotedPercent.PercentageOfSalesInJanuary 
    , PivotedMax.MaxSoldInNovember 
    , PivotedPercent.PercentageOfSalesInNovember 
    , PivotedMax.MaxSoldInDecember 
    , PivotedPercent.PercentageOfSalesInDecember 
FROM PivotedMax 
INNER JOIN PivotedPercent 
    ON PivotedPercent.Area = PivotedMax.Area 
     AND PivotedPercent.BuildingType = PivotedMax.BuildingType 

样本数据都是针对一个区域的，但它应该对很多人都适用。它也可以修改为按区域工作并取出建筑物类型分组。希望有所帮助。