数据挖掘Demo数据

--create table Oders (OdersID int  identity(1,1) primary key, UsersID int)
--create table OderIterms( OdersID int,ProductID int)
--create table Product(ProductID int identity(1,1) primary key , ProductName varchar(50) )
--create table Users(UsersID  int  identity(1,1) primary key,gender bit,age int )
--declare @i int
--set @i=0
----
--while @i< 10000
--begin
--insert Users select case when rand()>0.5 then 1 else 0 end  as gender,CAST( rand()*100 as int) as age
--set @i=@i+1
--end
----
----
--set @i=0
--while @i<10*10000
--begin
--insert Product select '产品'+ CAST( rand()*100 as varchar) as ProductName
--set @i=@i+1
--end
----
----
--set @i=0
--while @i<100*10000
--begin
--insert Oders select top 1 UsersID   as UsersID from Users order by NEWID()
--set @i=@i+1
--end
--
declare @i int
set @i=0
declare @maxUser int
select @maxUser= MAX(UsersID) from Users
 select CAST( RAND()*@maxUser as int)
declare @minUser int
select @minUser=Min(UsersID) from Users

declare @maxOders int
select @maxOders= MAX(OdersID) from Oders

declare @minOders int
select @minOders= Min(OdersID) from Oders

while @i<1000*10000
begin
insert OderIterms select
 @minUser+ CAST(RAND()*@maxUser as int) as OdersID ,
@minOders + CAST(RAND()*@maxOders as int) as OdersID
set @i=@i+1
end

--select COUNT(*) from dbo.OderIterms
--delete from dbo.Users
--delete from dbo.OderIterms
--delete from dbo.Oders
--delete from  dbo.Product 
--select COUNT(*) from  Product
--十万商品
declare @PID int
set @PID=12345
select top 10  A.productID,COUNT(A.productID) 数量
from  dbo.OderIterms A
inner join dbo.Oders B
on A.productID!=@PID
and B.UsersID in
(select C.Usersid from  dbo.Oders C inner join OderIterms D
on C.OdersID=D.OdersID where D.productID=@PID)
group by A.productID
order by 数量 desc

 
select   A.productID productID ,B.UsersID UsersID into #tb  from
OderIterms A  inner join Oders B
on A.OdersID=B.OdersID

select top 1 A.productID,B.productID,count(*) from #tb A inner join #tb B
on A.UsersID=B.UsersID and A.productID>B.productID
group by A.productID,B.productID
order by count(*)
 
  
 
 

转载于:https://www.cnblogs.com/dullwolf/archive/2011/07/22/2113434.html