Algorithm used to determine the number of clusters

Hello.

What kind of criterion is used by MS clustering algorithm to determine the number of clusters when 0 is specified in the algorithm parameters?

The problem is that I find automatically defined cluster number somehow strange, especially when expactation maximization algorithm is used. I tried to "manually" calculate optimal cluster numbers in my models using bayesian information criterion and the one by Akaike and received more understandable results.

Thank you very much in advance.

We do not specifically document the heuristic used in this case. The heuristic is intended for scenarios where users (often new to data mining) don't have specific expectations to be met: it provides a useful guess for these scenarios which are often rather diverse. We would not guarantee that it returns the optimal number of clusters in any given scenario.

Note that even setting the number of clusters explicitly is still an approximation, albeit for different reasons. As Books Online describes it, CLUSTER_COUNT "specifies the approximate number of clusters to be built by the algorithm. If the approximate number of clusters cannot be built from the data, the algorithm builds as many clusters as possible."

For the future, would be it be interesting or useful to have detailed, specified (anbd perhaps) selectable methods for cluster counts, such as those you used yourself?

hth

|||Yes, I think it would be interesting to have specified and selectable methods for automatic cluster selection. The reason is that cluster analysis is often used for unsupervised learning before any other methods are applied. Thus, in the first step little information about the data is available and one is interested in natural grouping or clustering in data. So the question "how many natural clusters exist in data" may be very important not less important than "how is data distributed within each cluster". That's why I think it would be useful to have some impact on algorithm used to answer this question.

Aggregate Strings While Aggregating Data

I'm using the following query to determine the TotTons produced daily. A
sample data set is included.
On days that two grades are produced, I'd like the Grade name to be a
combination of the two grade names separated by a \. For example the desire
d
Grade name for 08-01-05 would be H\V. At this point I'm not concerned about
the order in which the letters appear in the new string.
Is this possible?
Thanks in advance,
Raul
SELECT
MAX(Datestamp1) AS DateStamp,
SUM(Tons) AS TotTons,
MAX(Grade) AS Grade 'just for example
FROM
(
SELECT
DateStamp as Datestamp1,
NumBatches,
Grade,
Tons
FROM
DailyTonsByGrade
) inrqry
GROUP BY Datestamp1
ORDER BY DateStamp ASC;
CREATE TABLE DailyTonsByGrade (
DateStamp smalldatetime,
NumBatches int,
Grade Varchar(20),
Tons real)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('06-01-05', 48, 'V', 403.2)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('07-01-05', 62, 'V', 520.8)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('08-01-05', 12, 'H', 112.8)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('08-01-05', 31, 'V', 285.6)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('09-01-05', 44, 'H', 413.6)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('10-01-05', 60, 'H', 564.0)
Or
DateStamp NumBatches Grade Tons
06-01-05 48 V 403.2
07-01-05 62 V 520.8
08-01-05 12 H 112.8
08-01-05 34 V 285.6
09-01-05 44 H 413.6
10-01-05 60 H 564would you ever have more than 2 grades on the same day ?
Message posted via http://www.webservertalk.com|||
In order to do that you need code that processes the multiple records in one
day's group, and outputs a concatenation of the Grade NAmes...
It can be done, but it involves row-processing (using a cursor, or a temp
table or table Variable) probably in a Stored Proc, or User efined FUnction.
One solution(using Latter)
would be
Create Functiondbo.FuelGrades(@.Dt DateTime)
Returns VarChar(500)
As
Begin
Declare @.Out VarChar(500) Set @.Out = ''
Declare @.Grade VarChar(50) Set @.Grade = ''
While Exists
(Select * From DailyTonsByGrade
Where DateStamp = @.Dt
And Grade > @.Grade)
Select @.Out = @.Out + Min(Grade) + '/',
@.Grade = Min(Grade)
From DailyTonsByGrade
Where DateStamp = @.Dt
-- --
If Len(@.Out) > 0 Set @.Out = Left(@.Out, Len(@.Out) - 1)
Return @.Out
End
Then, in your query, just refer to this UDF...
Select Max(Datestamp) DateStamp,
Sum(Tons) TotTons,
dbo.FuelGrades(Datestamp) Grade
From DailyTonsByGrade
Group By Datestamp
Order By Max(Datestamp);
This will work, but performance will be poor...
"Raul" wrote:

> I'm using the following query to determine the TotTons produced daily. A
> sample data set is included.
> On days that two grades are produced, I'd like the Grade name to be a
> combination of the two grade names separated by a \. For example the desi
red
> Grade name for 08-01-05 would be H\V. At this point I'm not concerned abo
ut
> the order in which the letters appear in the new string.
> Is this possible?
> Thanks in advance,
> Raul
> SELECT
> MAX(Datestamp1) AS DateStamp,
> SUM(Tons) AS TotTons,
> MAX(Grade) AS Grade 'just for example
> FROM
> (
> SELECT
> DateStamp as Datestamp1,
> NumBatches,
> Grade,
> Tons
> FROM
> DailyTonsByGrade
> ) inrqry
> GROUP BY Datestamp1
> ORDER BY DateStamp ASC;
> CREATE TABLE DailyTonsByGrade (
> DateStamp smalldatetime,
> NumBatches int,
> Grade Varchar(20),
> Tons real)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('06-01-05', 48, 'V', 403.2)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('07-01-05', 62, 'V', 520.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 12, 'H', 112.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 31, 'V', 285.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('09-01-05', 44, 'H', 413.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('10-01-05', 60, 'H', 564.0)
> Or
> DateStamp NumBatches Grade Tons
> 06-01-05 48 V 403.2
> 07-01-05 62 V 520.8
> 08-01-05 12 H 112.8
> 08-01-05 34 V 285.6
> 09-01-05 44 H 413.6
> 10-01-05 60 H 564
>|||opps, I left out something...
THe UDF should be
Create Functiondbo.FuelGrades(@.Dt DateTime)
Returns VarChar(500)
As
Begin
Declare @.Out VarChar(500) Set @.Out = ''
Declare @.Grade VarChar(50) Set @.Grade = ''
While Exists
(Select * From DailyTonsByGrade
Where DateStamp = @.Dt
And Grade > @.Grade)
Select @.Out = @.Out + Min(Grade) + '/',
@.Grade = Min(Grade)
From DailyTonsByGrade
Where DateStamp = @.Dt
And Grade > @.Grade -- This is line I left Out
-- --
If Len(@.Out) > 0 Set @.Out = Left(@.Out, Len(@.Out) - 1)
Return @.Out
End
"Raul" wrote:

> I'm using the following query to determine the TotTons produced daily. A
> sample data set is included.
> On days that two grades are produced, I'd like the Grade name to be a
> combination of the two grade names separated by a \. For example the desi
red
> Grade name for 08-01-05 would be H\V. At this point I'm not concerned abo
ut
> the order in which the letters appear in the new string.
> Is this possible?
> Thanks in advance,
> Raul
> SELECT
> MAX(Datestamp1) AS DateStamp,
> SUM(Tons) AS TotTons,
> MAX(Grade) AS Grade 'just for example
> FROM
> (
> SELECT
> DateStamp as Datestamp1,
> NumBatches,
> Grade,
> Tons
> FROM
> DailyTonsByGrade
> ) inrqry
> GROUP BY Datestamp1
> ORDER BY DateStamp ASC;
> CREATE TABLE DailyTonsByGrade (
> DateStamp smalldatetime,
> NumBatches int,
> Grade Varchar(20),
> Tons real)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('06-01-05', 48, 'V', 403.2)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('07-01-05', 62, 'V', 520.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 12, 'H', 112.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 31, 'V', 285.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('09-01-05', 44, 'H', 413.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('10-01-05', 60, 'H', 564.0)
> Or
> DateStamp NumBatches Grade Tons
> 06-01-05 48 V 403.2
> 07-01-05 62 V 520.8
> 08-01-05 12 H 112.8
> 08-01-05 34 V 285.6
> 09-01-05 44 H 413.6
> 10-01-05 60 H 564
>|||if you are NEVER going to exceed 2 grade on any one day then this should
work
SELECT
MAX(Datestamp) AS DateStamp,
SUM(Tons) AS TotTons,
case
when MAX(Grade) <> Min(grade) then MAX(Grade)+ ''+ Min(grade)
else min(grade)
end AS Grade --just for example
FROM
DailyTonsByGrade
GROUP BY Datestamp
ORDER BY DateStamp ASC
Message posted via http://www.webservertalk.com|||It is unlikely that we will produce more than two grades in one day.
This is a pretty clever solution.
Thanks a bunch,
Raul
"baie dronk via webservertalk.com" wrote:

> if you are NEVER going to exceed 2 grade on any one day then this should
> work
> SELECT
> MAX(Datestamp) AS DateStamp,
> SUM(Tons) AS TotTons,
> case
> when MAX(Grade) <> Min(grade) then MAX(Grade)+ ''+ Min(grade)
> else min(grade)
> end AS Grade --just for example
> FROM
> DailyTonsByGrade
> GROUP BY Datestamp
> ORDER BY DateStamp ASC
> --
> Message posted via http://www.webservertalk.com
>|||I'll try this solution also.
Thank you,
Raul
"CBretana" wrote:
> opps, I left out something...
> THe UDF should be
> Create Functiondbo.FuelGrades(@.Dt DateTime)
> Returns VarChar(500)
> As
> Begin
> Declare @.Out VarChar(500) Set @.Out = ''
> Declare @.Grade VarChar(50) Set @.Grade = ''
> While Exists
> (Select * From DailyTonsByGrade
> Where DateStamp = @.Dt
> And Grade > @.Grade)
> Select @.Out = @.Out + Min(Grade) + '/',
> @.Grade = Min(Grade)
> From DailyTonsByGrade
> Where DateStamp = @.Dt
> And Grade > @.Grade -- This is line I left Out
> -- --
> If Len(@.Out) > 0 Set @.Out = Left(@.Out, Len(@.Out) - 1)
> Return @.Out
> End
>
> "Raul" wrote:
>