Is there a better way to build discretization buckets

• drew.georgopulos

  SSCarpal Tunnel

  Points: 4931

  More actions

  August 25, 2011 at 11:55 am

  #148590

  than just murderlizing them into a table? granted, the scheme is slightly irrational, but something nags me that if i were a bit (or three) smarter there is a way to do it with partition/order by?

  CREATE TABLE [dbo].[CostCategories](

  [CostCategory] [varchar](100) NULL,

  [LowRange] [money] NULL,

  [HighRange] [money] NULL,

  [id] [int] NULL

  ) ON [PRIMARY]

  select 'Less Than $0','-99999999.00','-0.01','1'union all

  select '$0 To $499','0.01','499.99','2'union all

  select '$500 to $999','500.00','999.99','3'union all

  select '$1,000 to $2,499','1000.00','2499.99','4'union all

  select '$2,500 to $4,999','2500.00','4999.99','5'union all

  select '$5,000 to $9,999','5000.00','9999.99','6'union all

  select '$10,000 to $24,999','10000.00','24999.99','7'union all

  select '$25,000 to $49,999','25000.00','49999.99','8'union all

  select '$50,000 to $99,999','50000.00','99999.99','9'union all

  select '$100,000 to $249,999','100000.00','249999.99','10'union all

  select '$250,000 to $499,999','250000.00','499999.99','11'union all

  select '$500,000 to $999,999','500000.00','999999.99','12'union all

  select '$1,000,000+','1000000.00','999999999.99','13'union all

  select '$0','0.00','0.00','14

  thanks a lot

  drew

• drew.georgopulos

  SSCarpal Tunnel

  Points: 4931

  More actions

  August 25, 2011 at 6:17 pm

  #1373557

  something LIKE this, but not this, because power(log(row_number... seems to make the increment lope along, but not with the characteristics of the first table;

  declare @tally table (N int)

  insert @tally

  select 1 union all

  select 1 union all

  select 1 union all

  select 1

  --that's four rows in @tally, so cross join with itself

  declare @tally2 table (N int,factor int, increment int)

  insert @tally2 select a.n,

  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) ,

  power(LOG(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))),ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))

  from @tally a cross join @tally b

  i may be totally wrong but im a...

• drew.georgopulos

  SSCarpal Tunnel

  Points: 4931

  More actions

  August 29, 2011 at 10:08 pm

  #1374805

  thanks to y'all, i was able to answer my own question...took two days, but i work cheap.

  i found the answer by construction;

  I graphed the original data in Excel

  I added an exponential trend line because it fit the graph best

  I let Excel add the equation of the exponential graph and stole it.

  --make ten points for the x axis;

  declare @@tally table (N int)

  insert @@tally

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1

  --use the power function mimic Excel's line to fill in the Y values for each X

  select power(4,1*ROW_NUMBER()

  OVER (ORDER BY (SELECT NULL)))

  from @@tally

  worked pretty good.

  thanks very much

  drew

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  August 31, 2011 at 7:07 am

  #1375443

  I think this problem could also be solved with my favorite 4-letter TSQL word: CASE. That is a nice solution you have too, btw.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  August 31, 2011 at 7:30 am

  #1375463

  drew.georgopulos (8/29/2011)

  ---

  thanks to y'all, i was able to answer my own question...took two days, but i work cheap.

  i found the answer by construction;

  I graphed the original data in Excel

  I added an exponential trend line because it fit the graph best

  I let Excel add the equation of the exponential graph and stole it.

  --make ten points for the x axis;

  declare @@tally table (N int)

  insert @@tally

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1 union all

  select 1

  --use the power function mimic Excel's line to fill in the Y values for each X

  select power(4,1*ROW_NUMBER()

  OVER (ORDER BY (SELECT NULL)))

  from @@tally

  worked pretty good.

  thanks very much

  drew

  Slight improvement : -

  ;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),

  t2 AS (SELECT 1 N FROM t1 x, t1 y),

  tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn

  FROM t2 x, t2 y)

  SELECT power(4,rn)

  FROM tally

  WHERE rn <= 10

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• drew.georgopulos

  SSCarpal Tunnel

  Points: 4931

  More actions

  August 31, 2011 at 9:00 am

  #1375539

  TheSQLGuru (8/31/2011)

  ---

  I think this problem could also be solved with my favorite 4-letter TSQL word: CASE. That is a nice solution you have too, btw.

  thank you very much, i appreciate it.

• drew.georgopulos

  SSCarpal Tunnel

  Points: 4931

  More actions

  August 31, 2011 at 9:02 am

  #1375541

  Slight improvement : -

  ;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),

  t2 AS (SELECT 1 N FROM t1 x, t1 y),

  tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn

  FROM t2 x, t2 y)

  SELECT power(4,rn)

  FROM tally

  WHERE rn <= 10

  Wow, yours is so much more elegant than mine, thank you very much, its beautiful!