PMML: One node in a decision tree containing two states of an attribute as the rule for splittin

Hi,
is there a way to import a decision tree-model from pmml where a node contains two or more states of an attribute as the split-rule?

Example:

...
<Node recordCount="600">
<CompoundPredicate booleanOperator="or">
<SimplePredicate field="color" operator="equal" value="red" />
<SimplePredicate field="color" operator="equal" value="green" />
</CompoundPredicate>
<ScoreDistribution value="true" recordCount="200"/>
<ScoreDistribution value="false" recordCount="400"/>
</Node>
...

This node shoud contain all cases, whose color is red or green (The Microsoft DecisionTree-Algorithm would build a model with two steps like red/ not red and then green / not green). According to the DMG, this is valid PMML 2.1, but when trying to import the server complains about an unexpected value in the SimplePredicate-tag.

How can i import such a node in SqlServer 2005?

Thank you in advance for any help

Chris

No, the Microsoft_Decision_Trees algorithm does not support splits of this type.|||You could however, implement the prediction logic of a tree as a plug-in algorithm and have your implementation parse PMML bodies with OR conditions.|||Ok, maybe we will try that. Thanks for your answer!

PMML: One node in a decision tree containing two states of an attribute as the rule for splittin

Hi,
is there a way to import a decision tree-model from pmml where a node contains two or more states of an attribute as the split-rule?

Example:

...
<Node recordCount="600">
<CompoundPredicate booleanOperator="or">
<SimplePredicate field="color" operator="equal" value="red" />
<SimplePredicate field="color" operator="equal" value="green" />
</CompoundPredicate>
<ScoreDistribution value="true" recordCount="200"/>
<ScoreDistribution value="false" recordCount="400"/>
</Node>
...

This node shoud contain all cases, whose color is red or green (The Microsoft DecisionTree-Algorithm would build a model with two steps like red/ not red and then green / not green). According to the DMG, this is valid PMML 2.1, but when trying to import the server complains about an unexpected value in the SimplePredicate-tag.

How can i import such a node in SqlServer 2005?

Thank you in advance for any help

Chris

No, the Microsoft_Decision_Trees algorithm does not support splits of this type.|||You could however, implement the prediction logic of a tree as a plug-in algorithm and have your implementation parse PMML bodies with OR conditions.|||Ok, maybe we will try that. Thanks for your answer!

PMML: One node in a decision tree containing two states of an attribute as the rule for spli

Hi,
is there a way to import a decision tree-model from pmml where a node contains two or more states of an attribute as the split-rule?

Example:

...
<Node recordCount="600">
<CompoundPredicate booleanOperator="or">
<SimplePredicate field="color" operator="equal" value="red" />
<SimplePredicate field="color" operator="equal" value="green" />
</CompoundPredicate>
<ScoreDistribution value="true" recordCount="200"/>
<ScoreDistribution value="false" recordCount="400"/>
</Node>
...

This node shoud contain all cases, whose color is red or green (The Microsoft DecisionTree-Algorithm would build a model with two steps like red/ not red and then green / not green). According to the DMG, this is valid PMML 2.1, but when trying to import the server complains about an unexpected value in the SimplePredicate-tag.

How can i import such a node in SqlServer 2005?

Thank you in advance for any help

Chris

No, the Microsoft_Decision_Trees algorithm does not support splits of this type.|||You could however, implement the prediction logic of a tree as a plug-in algorithm and have your implementation parse PMML bodies with OR conditions.|||Ok, maybe we will try that. Thanks for your answer!

PLZ HELP: number of calls at a time

I have a traffic table which contains the following field:

START_DATE_TIME (e.g. 2/14/2006 7:19:54 AM)

and I want to the following;

how many calls we have every minute (count) starting from 00:00 to 23:59?

what's the maximam number of calls and what time was it?

-- Create tets table

create table #trafic(

id int identity not null primary key,

start datetime

)

go

--Insert sample data

insert into #trafic values(getdate())

insert into #trafic values(getdate())

insert into #trafic values(dateadd(hour, 1, getdate()))

insert into #trafic values(dateadd(hour, 2, getdate()))

insert into #trafic values(dateadd(hour, 3, getdate()))

insert into #trafic values(dateadd(minute, 10, getdate()))

insert into #trafic values(dateadd(minute, 10, getdate()))

--Show my sample date

select * from #trafic

--Result

id start

-- --

1 2007-03-06 11:14:28.983

2 2007-03-06 11:14:29.000

3 2007-03-06 12:14:29.000

4 2007-03-06 13:14:29.000

5 2007-03-06 14:14:29.000

6 2007-03-06 11:24:29.000

7 2007-03-06 11:24:29.000

--Calls per minute

select count(id)

, cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2)) --Expression for getting hour:minute part of call datetime

from #trafic

group by cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2))

--Result

-- --

2 11:14

2 11:24

1 12:14

1 13:14

1 14:14

(5 row(s) affected)

--Show max calls

select top 1 count(id)

, cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2))

from #trafic

group by cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2))

order by count(id) desc

--Results

-- --

2 11:24

(1 row(s) affected)

--If few minutes have maximuns call number

select top 1 with ties count(id)

, cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2))

from #trafic

group by cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2))

order by count(id) desc

--Result

-- --

2 11:14

2 11:24

(2 row(s) affected)

Pls help. Cannot index the view...It contains one or more disallowed constructs

Thank you for your help.
What am I missing here?
I read the BOL. I just dont see what is the problem.
SET ANSI_PADDING ON
GO
SET ANSI_PADDING ON
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[OrderLineSAItems]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[OrderLineSAItems] (
[olnID] [int] NOT NULL ,
[saiID] [int] NOT NULL ,
[olnsQuantity] [int] NOT NULL
CONSTRAINT [PK_OrderLineSAItems_olnID] PRIMARY KEY CLUSTERED
(
[olnID]
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[OrderLineOptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[OrderLineOptions] (
[olnID] [int] NOT NULL ,
[optID] [int] NOT NULL ,
[olnoValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[olnoEngValue] [decimal](19, 6) NULL ,
CONSTRAINT [PK_OrderLineOptions_olnID_optID] PRIMARY KEY CLUSTERED
(
[olnID],
[optID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
IF OBJECT_ID('dbo.OrderLineOptionChecksums') IS NOT NULL and
objectproperty(OBJECT_ID('dbo.OrderLineOptionChecksums'),'IsView') = 1
DROP VIEW dbo.OrderLineOptionChecksums
GO
CREATE VIEW dbo.OrderLineOptionChecksums
WITH SCHEMABINDING
AS
SELECT
olns.olnID
,CHECKSUM_AGG(
binary_checksum (
olns.saiID
, olno.optID
, olno.olnoValue
, olno.olnoEngValue
)
)
as OptionsChecksum
,count_big(*) as CntBig
FROM dbo.OrderLineSAItems olns
JOIN dbo.OrderLineOptions olno ON olno.olnID = olns.olnID
GROUP BY olns.olnID
GO
CREATE UNIQUE CLUSTERED INDEX UX_OrderLineOptionChecksums_olnID ON
dbo.OrderLineOptionChecksums ( olnID )
go
CREATE NONCLUSTERED INDEX IX_OrderLineOptionChecksums_OptionsCheck
sum ON
dbo.OrderLineOptionChecksums ( OptionsChecksum )
go
-- $Log$
GOThank you, Jacco, for your explanation. It's not what I wanted to hear
though. I think it's also quite a limitation. That means that I need to use
trigger to maintain this checksum.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23eiAV$0UFHA.3076@.TK2MSFTNGP12.phx.gbl...
>I think the problem is with CHECKSUM_AGG. The idea behind the
>implementation of indexed views is that when a row is
>inserted/updated/deleted in one of the underlying tables, the new values in
>the indexed views can be calculated from just the changes in the underlying
>tables, without having to access any other rows in the table(s). I don't
>think this is the case with CHECKSUM_AGG, or in other words, if for example
>you delete a row from a table, you can't calculate the new value for the
>CHECKSUM_AGG by deducting the checksum for the deleted row from the value
>that was in the view previously. You would have to access the other rows in
>the table to recalculate the checksum.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23YeZeqyUFHA.1044@.TK2MSFTNGP10.phx.gbl...
>|||Jacco
I was just reading more on indexed views and I have a question.
How would you explain
SUM(X), COUNT_BIG(X)
being allowed then? It has to go and re-read all other rows to get a new sum
if a row is deleted/updated/inserted.
Thanks
Vlad
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23eiAV$0UFHA.3076@.TK2MSFTNGP12.phx.gbl...
>I think the problem is with CHECKSUM_AGG. The idea behind the
>implementation of indexed views is that when a row is
>inserted/updated/deleted in one of the underlying tables, the new values in
>the indexed views can be calculated from just the changes in the underlying
>tables, without having to access any other rows in the table(s). I don't
>think this is the case with CHECKSUM_AGG, or in other words, if for example
>you delete a row from a table, you can't calculate the new value for the
>CHECKSUM_AGG by deducting the checksum for the deleted row from the value
>that was in the view previously. You would have to access the other rows in
>the table to recalculate the checksum.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23YeZeqyUFHA.1044@.TK2MSFTNGP10.phx.gbl...
>|||No, because if you add a row (or multiple rows) to a base table for an
indexed view you can calculate the new values for SUM and BIG_COUNT from the
existing values for SUM and BIG_COUNT and the values for the inserted
row(s).
If you have a table
CREATE TABLE t(i INT IDENTITY PRIMARY KEY, v INT NOT NULL)
with an indexed view SELECT SUM(v) as sum_v, COUNT_BIG(*) as cnt
If you have 2 rows in the table:
1,2 and 2,3
The indexed view will have the values: 5, 2
When you insert another row into the table with v = 4, you can update the
view by adding 4 to the value for sum_v that is already there (5), and
increasing the value for cnt by 1. There is no need to revisit the rows that
are already in the table. This doesn't work for CHECKSUM_AGG though.
Jacco Schalkwijk
SQL Server MVP
"Farmer" <someone@.somewhere.com> wrote in message
news:%23IgX3PYVFHA.544@.TK2MSFTNGP15.phx.gbl...
> Jacco
> I was just reading more on indexed views and I have a question.
> How would you explain
> SUM(X), COUNT_BIG(X)
> being allowed then? It has to go and re-read all other rows to get a new
> sum if a row is deleted/updated/inserted.
> Thanks
> Vlad
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23eiAV$0UFHA.3076@.TK2MSFTNGP12.phx.gbl...
>|||Thank you.
Your explanation makes total sense and your logic is very sound.
However, I am still disapointed that this is the case, even though you are
right, that SQL does not deal with this problem.
It would have been such a powerful feature.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:OvC5WzYVFHA.3544@.TK2MSFTNGP12.phx.gbl...
> No, because if you add a row (or multiple rows) to a base table for an
> indexed view you can calculate the new values for SUM and BIG_COUNT from
> the existing values for SUM and BIG_COUNT and the values for the inserted
> row(s).
> If you have a table
> CREATE TABLE t(i INT IDENTITY PRIMARY KEY, v INT NOT NULL)
> with an indexed view SELECT SUM(v) as sum_v, COUNT_BIG(*) as cnt
> If you have 2 rows in the table:
> 1,2 and 2,3
> The indexed view will have the values: 5, 2
> When you insert another row into the table with v = 4, you can update the
> view by adding 4 to the value for sum_v that is already there (5), and
> increasing the value for cnt by 1. There is no need to revisit the rows
> that are already in the table. This doesn't work for CHECKSUM_AGG though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23IgX3PYVFHA.544@.TK2MSFTNGP15.phx.gbl...
>|||Yes, but it wouldn't have been possible to have all the performance
optimizations that indexed views provide if constructs like CHECKSUM_AGG
were allowed. And you can still do what you want with a non-indexed view or
a trigger.
Jacco Schalkwijk
SQL Server MVP
"Farmer" <someone@.somewhere.com> wrote in message
news:efPG8QZVFHA.548@.tk2msftngp13.phx.gbl...
> Thank you.
> Your explanation makes total sense and your logic is very sound.
> However, I am still disapointed that this is the case, even though you are
> right, that SQL does not deal with this problem.
> It would have been such a powerful feature.
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:OvC5WzYVFHA.3544@.TK2MSFTNGP12.phx.gbl...
>

Pls Help With JOIN query...

I'm trying to write a stored proc...

Basically, I have a tblItems table which contains a list of every item
available. One of the columns in this table is the brand... for test
purposes, I hardcoded the BrandID=1...

tblItems also contains a category column (int) which contains a categoryID
of 0..3...

I then have a category table which has CategoryID, Name, and DisplayOrder...

So basically what I'm trying to do is return a list of Category NAMES that
have items in them for a specifc brand... but I want to sort the returned
categories by the DisplayOrder column...

this is what I have now:

select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by tblCategories.DisplayOrder

this does what I want it to do, but its returning TWO columns... Name AND
DisplayOrder... I only want to return Name, but if I take the DisplayOrder
out of the select portion, it errors out because it can't order by that...

Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies of
the same category name.Just remove the order by ie:

select DISTINCT tblCategories.Name, tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1
/* order by tblCategories.DisplayOrder */

Nobody wrote:

Quote:

Originally Posted by

I'm trying to write a stored proc...
>
Basically, I have a tblItems table which contains a list of every item
available. One of the columns in this table is the brand... for test
purposes, I hardcoded the BrandID=1...
>
tblItems also contains a category column (int) which contains a categoryID
of 0..3...
>
I then have a category table which has CategoryID, Name, and DisplayOrder...
>
So basically what I'm trying to do is return a list of Category NAMES that
have items in them for a specifc brand... but I want to sort the returned
categories by the DisplayOrder column...
>
this is what I have now:
>
>
select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by tblCategories.DisplayOrder
>
this does what I want it to do, but its returning TWO columns... Name AND
DisplayOrder... I only want to return Name, but if I take the DisplayOrder
out of the select portion, it errors out because it can't order by that...
>
Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies of
the same category name.

|||Okay my mistake. This will do the job:

select a.tblCategories.Name
from (select DISTINCT top 100 percent tblCategories.Name,
tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by
tblCategories.Name,tblCategories.DisplayOrder) a

Unlike other databases, SQL Server does not allow 'order by' within
derived tables, so had to use top etc...

othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

Just remove the order by ie:
>
select DISTINCT tblCategories.Name, tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1
/* order by tblCategories.DisplayOrder */
>
Nobody wrote:

Quote:

Originally Posted by

I'm trying to write a stored proc...

Basically, I have a tblItems table which contains a list of every item
available. One of the columns in this table is the brand... for test
purposes, I hardcoded the BrandID=1...

tblItems also contains a category column (int) which contains a categoryID
of 0..3...

I then have a category table which has CategoryID, Name, and DisplayOrder...

So basically what I'm trying to do is return a list of Category NAMES that
have items in them for a specifc brand... but I want to sort the returned
categories by the DisplayOrder column...

this is what I have now:

select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by tblCategories.DisplayOrder

this does what I want it to do, but its returning TWO columns... Name AND
DisplayOrder... I only want to return Name, but if I take the DisplayOrder
out of the select portion, it errors out because it can't order by that...

Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies of
the same category name.

|||Nobody (nobody@.cox.net) writes:

Quote:

Originally Posted by

I'm trying to write a stored proc...
>
Basically, I have a tblItems table which contains a list of every item
available. One of the columns in this table is the brand... for test
purposes, I hardcoded the BrandID=1...
>
tblItems also contains a category column (int) which contains a categoryID
of 0..3...
>
I then have a category table which has CategoryID, Name, and
DisplayOrder...
>
So basically what I'm trying to do is return a list of Category NAMES that
have items in them for a specifc brand... but I want to sort the returned
categories by the DisplayOrder column...
>
this is what I have now:
>
>
select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by tblCategories.DisplayOrder
>
this does what I want it to do, but its returning TWO columns... Name AND
DisplayOrder... I only want to return Name, but if I take the DisplayOrder
out of the select portion, it errors out because it can't order by that...
>
Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies
of the same category name.

No, you don't need DISTINCT. You need to learn to use EXISTS:

SELECT C.Name
FROM tblCategories C
WHERE EXISTS (SELECT *
FROM tblItems I
WHERE I.CategoryID = C.CategoryID
AND I.BrandID = @.brandid)
ORDER BY C.DisplayOrder

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Wed, 22 Nov 2006 16:59:17 -0800, Nobody wrote:

Quote:

Originally Posted by

>I'm trying to write a stored proc...
>
>Basically, I have a tblItems table which contains a list of every item
>available. One of the columns in this table is the brand... for test
>purposes, I hardcoded the BrandID=1...
>
>tblItems also contains a category column (int) which contains a categoryID
>of 0..3...
>
>I then have a category table which has CategoryID, Name, and DisplayOrder...
>
>So basically what I'm trying to do is return a list of Category NAMES that
>have items in them for a specifc brand... but I want to sort the returned
>categories by the DisplayOrder column...
>
>this is what I have now:
>
>
>select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
>tblCategories
>INNER JOIN tblItems
>on tblCategories.CategoryID = tblItems.CategoryID
>where BrandID=1 order by tblCategories.DisplayOrder
>
>this does what I want it to do, but its returning TWO columns... Name AND
>DisplayOrder... I only want to return Name, but if I take the DisplayOrder
>out of the select portion, it errors out because it can't order by that...
>
>Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies of
>the same category name.
>

Hi Nobody,

Since you don't display any columns from tblItems, the only reason to
use it in this query is obviously to check for existance of at least one
row with BrandID equal to 1. That means that you can rewrite your query
as

SELECT c.Name --, c.DisplayOrder
FROM Categories AS c
WHERE EXISTS
(SELECT *
FROM Items AS i
WHERE i.CategoryID = c.CategoryID
AND i.BrandID = 1)
ORDER BY c.DisplayOrder;

You'll probably see a performance increase as well.

--
Hugo Kornelis, SQL Server MVP|||On 23 Nov 2006 01:48:32 -0800, othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

>Okay my mistake. This will do the job:
>
>select a.tblCategories.Name
>from (select DISTINCT top 100 percent tblCategories.Name,
>tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by
>tblCategories.Name,tblCategories.DisplayOrder) a
>
>Unlike other databases, SQL Server does not allow 'order by' within
>derived tables, so had to use top etc...

Hi othellomy,

Though you can use ORDER BY in a subquery if you also use TOP, the ORDER
BY will only be used to determins which rows meat the TOP criterium;
there is no guarantee that the actual order of the query will be the
same. In fact, SQL Server 2005 will ignore both TOP 100 PERCENT and the
accomanying ORDER BY, since it is essentially a no-op to restrict the
output to 100 percent of the regular output.

If you really want to move the DISTINCT to a subquery (which in this
case is NOT needed - see my reply to Nobody), you could use

SELECT a.Name
FROM (SELECT DISTINCT c.Name, c.DisplayOrder
FROM Categories AS c
INNER JOIN Items AS i
ON i.CategoriID = c.CategoryID
WHERE i.BrandID = 1) AS a
ORDER BY a.DisplayOrder;

(untested)

--
Hugo Kornelis, SQL Server MVP