Dear sir,

We have a banner system which we like to show banner based on a weight
assigned to it. The more weight a banner received, the higher possibility
this banner will be selected.

So the table is:

BannerId, Content, Weight.
1          xx22     4
2          xx333    6

So for every 100 times, in theory, banner 2 will be shown 60 times and
banner 1 will be shown 40 times.

How can I have a SQL statement to select this?

Thanks!

Guoqi Zheng wrote:
Show quoteHide quoteSome variation of this:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/SelectingRandomRecords

Maybe wrapping CHECKSUM around the NEWID() value, multiplied by the weight?

Thanks for your answer, I knew the solution of using "Order by newid()".
Hoever that does not solve the problem because I need to order them based on
the weigth as well.

Any one, Any other solutions?


??????:e5OhxpKvGHA.4***@TK2MSFTNGP04.phx.gbl...
Show quoteHide quote

Guoqi Zheng wrote:
Did you READ my entire post?  Try:

SELECT TOP 1
    BannerID, Content
FROM Banners
ORDER BY ABS(CHECKSUM(NEWID()) * CONVERT(BIGINT, Weight)

This seems to work now. only I have to add a DESC at the end.

I will check it out carefully. Thanks a lot.

??????:O6ETy2KvGHA.4***@TK2MSFTNGP02.phx.gbl...
Show quoteHide quote

Will this work for banners of equal weights?
Show quoteHide quote

Reg Besseling wrote:
Define "work".  Given two banners with equal weights, it will randomly
return one of the two.

while trying to figure out what  your solution does and why it appeared to
work i ran it 100000 times for weights of 10 and 90  and it returned the 90
weight 94.5% of the time.

i did not try this for mare than 2 banners

when the weights are equal or near equal the diffrence appears to becomes
less.


Show quoteHide quote

Hi Guoqi

The only way i could think of doing this was to slightly change your table
from having a only a weight column  to having a start and end weight.

they should not overlap

anyway try this

CREATE TABLE [dbo].[Banners](
    [BannerID] [int] IDENTITY(1,1) NOT NULL,
    [BannerContent] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [StartWeight] [int] NOT NULL,
    [EndWeight] [int] NOT NULL
) ON [PRIMARY]

GO

INSERT INTO Banners values ('1XXXX',1,40)
INSERT INTO Banners values ('2YYYY',41,100)

go

create procedure usp_GetBanner as
declare @banner int
set @banner = (CAST((rand() * 100) as int) + 1)
SELECT  BannerID, BannerContent, StartWeight, EndWeight
from banners
where @banner between StartWeight and EndWeight
go
exec usp_GetBanner

Show quoteHide quote

Show quote Hide quote How's this?


create table Banners
(
  BannerId int primary key,
  Content varchar(10),
  Weight int
)

insert into Banners(BannerID, Content, Weight)
values (1,'xx22',4)

insert into Banners(BannerID, Content, Weight)
values (2,'xx333',6)

insert into Banners(BannerID, Content, Weight)
values (3,'xx333',10)

create index ix_banners_Weight on Banners(Weight)


set nocount on
go
drop table Utils.SequenceTable
go
create table Utils.SequenceTable(Value int primary key)
begin transaction
declare @i int
set @i = 0
while @i < 10000
begin
  insert into Utils.SequenceTable(Value) values (@i)
  set @i = @i + 1
end
commit transaction

go


select top 1 BannerID
from
Banners
cross join Utils.SequenceTable st
where st.Value <= Banners.Weight
order by newid()


David

Show quote Hide quote SB

select top 1 BannerID
from
Banners
cross join Utils.SequenceTable st
where st.Value < Banners.Weight
order by newid()


David

If you can handle this task in your application instead, I think that
would be better. Just because something CAN be done in SQL doesn't mean
it should be. This one is borderline inappropriate imho.

When non-standard solutions like "ORDER BY NewID()" appear, although
being seemingly neat and all, they generally tell you that a SQL
solution is not the way to go.

One of the few exceptions I can think of, when processing data *in the
database* IS called for, is when you need the data pre-processed for
reports in e.g. Reporting Services.

That said, one's SQL skills generally decide one's preference.

Here is a possible approach that is complicated, but once it's
in place, you can modify the weights or add or delete banners
without much trouble.  It is written for SQL Server 2005, but
with a little work, it can be modified to work with SQL Server
2000.  (The WITH is easy to replace with derived tables, but
the row_number() part has to be rewritten more substantially.)

Also, this will only be efficient if the number of banners and their
weights are relatively small.  I have written it for categories of
banners, where you want to choose one from a particular
category with the appropriate weights.

create table BannerWeights (
  BannerCategory int not null,
  BannerId int not null primary key,
  Weight int not null check (Weight between 0 and 256)
)
go

create table BannerHidden (
  BannerCategory int not null,
  BannerId int not null
)
create clustered index BannerHidden on BannerHidden(BannerCategory,BannerId)
go

create trigger BannerWeightsTrig on BannerWeights
for update, insert, delete as
delete from BannerHidden
where BannerId in (
  select BannerId from deleted
);
with D(d) as (
  select cast(null as tinyint) as d union all
  select null union all select null union all
  select null
)
  insert into BannerHidden
  select BannerCategory, BannerId
  from inserted cross apply (
    select top (Weight) D1.d
    from D as D1 cross join D as D2
      cross join D as D3 cross join D as D4
  ) as W
go

insert into BannerWeights values (1,1,4)
insert into BannerWeights values (1,2,6)
insert into BannerWeights values (1,3,5)
insert into BannerWeights values (1,4,10)
insert into BannerWeights values (1,5,5)
insert into BannerWeights values (1,6,11)
insert into BannerWeights values (2,21,10)
insert into BannerWeights values (2,23,5)
insert into BannerWeights values (3,30,1)
update BannerWeights set Weight = 0
  where BannerId = 3
update BannerWeights set Weight = 7
  where BannerId = 5
delete from BannerWeights
  where BannerId = 1
go

-- Check that this contains what's expected
select * from BannerHidden
go

create proc getBannerId (
  @category int,
  @id int output
) as
declare @f float
set @f = 1e0*abs(binary_checksum(newid()))/2147483647e0
set @id = (
  select top (1) BannerId from (
    select
      BannerId,
      count(*) over () as tableCt,
      row_number() over (order by BannerId) as rk
    from BannerHidden
    where BannerCategory = @category
  ) BH
  where 1e0*rk/tableCt > @f
  order by BannerId
)
go

declare @iter int set @iter = 30
declare @category int set @category = 3
declare @id int
while @category > 0 begin
  print 'Category #' + rtrim(@category)
  while @iter > 0 begin
    set @iter = @iter - 1
    exec getBannerId
      @category = @category,
      @id = @id output
    print 'Selected banner #' + rtrim(@id)
  end
  set @category = @category - 1
  set @iter = 30
end

go
drop proc getBannerId
drop table BannerHidden, BannerWeights

-- Steve Kass
-- Drew University
-- http://www.stevekass.com


Guoqi Zheng wrote:

Show quoteHide quote

Timeout expire
Optimize this query
TOP 50 invoices by company
How can I insert a new field between existing field?
store results over network