Please try my solution before you tell me it's broken, OK? I know you want
to see the top 10 spammers for EACH day. That's what I wrote for you.
Please try my solution with your data and get back to me with the results
and explain to me what's wrong so I can fix it.
Thank you for your patience,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Seena Blace <[EMAIL PROTECTED]> wrote on 05/16/2005 04:41:19 PM:
> Shawn,
> query
> SELECT *
> FROM spam_stats
> WHERE rank <= 10;
> will return all rows which I don't want.
> I need datewise top 10 spam domain.
> means condition would be serach those rows which are having top 10
> spam (means highest) on each day and show the output like which I
> send earlier.
> thanks
>
> [EMAIL PROTECTED] wrote:
> Seena Blace wrote on 05/16/2005 11:13:48 AM:
>
> > shawn
> > I think 2nd query will return only 10 rows.
> > I want out like followings
> > date domain spam
> > 05/05/05 hotmail.com 120
> > 05/05/05 yahoo.com 110
> > 05/05/05 abc.com 99
> > 05/05/05 def.com 80
> > 05/05/05 mnpo.net 79
> > ......................................... like that upto 10
> > --------------------------------------------------
> > 05/06/05 yahoo.com 300
> > 05/06/05 def.com 250
> > 05/06/05 zer.com 200
> > ..................................like that upto 10
> >
> > Each day there are multiple entry from diffrent domains or same
domain.
> > I want each day whatever top 10 spam sender domain.
> > thanks
> >
> >
> >
> >
> > [EMAIL PROTECTED] wrote:
> > Seena Blace wrote on 05/16/2005 10:08:15 AM:
> >
> > > Any suggestion pl?
> > >
> > > Seena Blace wrote:hi,
> > > here is table description
> > > report1
> > >
> > > +-------------------------+------------------+------+-----
> > > +---------------------+----------------+
> > > | Field | Type | Null | Key | Default | Extra |
> > > +-------------------------+------------------+------+-----
> > > +---------------------+----------------+
> > > | id | int(10) unsigned | | PRI | NULL | auto_increment |
> > > | host_id | int(10) unsigned | | MUL | 0 | |
> > > | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | |
> > > | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | |
> > > | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | |
> > > | time | datetime | | MUL | 0000-00-00 00:00:00 | |
> > > | detected_spam | int(10) unsigned | | | 0 | |
> > > | detected_virus | int(10) unsigned | | | 0 | |
> > > | processed | int(10) unsigned | | | 0 | |
> > > | allowed | int(10) unsigned | | | 0 | |
> > > | suspected | int(10) unsigned | | | 0 | |
> > > | blocked | int(10) unsigned | | | 0 | |
> > > | spam | int(10) unsigned | | | 0 | |
> > > | virus | int(10) unsigned | | | 0 | |
> > >
> > >
> > > I WANT REPORT LIKE FOLLOWINGS
> > >
> > > date sender processed spam suspected
> > >
> > >
> > > I want top 10 spam sender each day.
> > >
> > > QUery i'm using
> > > select date_format(time,'%Y-%d-%m'),report_sender_domain_id,
> > > processed ,spam from report1
> > > order by spam desc ,report_sender_domain_id,date_format(time,'%Y-%
> > > d-%m') limit 10;
> > >
> > >
> > >
> > > Please suggest.
> > > thanks
> > >
> > It was the weekend. Not everyone lurks on their days off.
> >
> > Try this
> > select date_format(time,'%Y-%d-%m')
> > ,report_sender_domain_id
> > ,processed
> > ,spam
> > ,suspected
> > from report1
> > order by spam desc
> > ,report_sender_domain_id
> > ,date_format(time,'%Y-%d-%m')
> > limit 10;
> >
> > or if there is more than one entry per spammer per day
> >
> > SELECT date_format(time,'%Y-%d-%m')
> > ,report_sender_domain_id
> > ,sum(processed) as processed
> > ,sum(spam) as spam
> > ,sum(suspected) as suspected
> > FROM report1
> > GROUP BY date_format(time,'%Y-%d-%m')
> > ,report_sender_domain_id
> > ORDER BY spam desc
> > ,report_sender_domain_id
> > ,date_format(time,'%Y-%d-%m')
> > LIMIT 10;
> >
> > That will give you their total stats for each day.
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> >
> > ---------------------------------
>
> OK, then what you want to do will take two steps and another table
(unless
> you want to write application code) to do with MySQL;
>
> First step is to create a table where the intermediate results can be
> stored. What this table does is number each row for each day. Since it
> appears that you may want this information frequently (but only need to
> update it at the end of every day) I will make this a permanent table
(not
> a temporary table).
>
> CREATE TABLE spam_stats (
> report_date date not null,
> report_sender_domain_id int unsigned not null,
> rank int unsigned auto_increment,
> processed int unsigned,
> spam int unsigned,
> suspected int unsigned,
> PRIMARY KEY (report_date, rank),
> UNIQUE KEY (report_sender_domain_id, report_date),
> KEY (rank)
> );
>
> The UNIQUE key ensures that the same spammer cannot have more than one
> entry per day while the PRIMARY KEY allows for a groupwize autonumber
> (each entry per day gets it's own number, rank, starting at 1). The last
> key is optional but will seriously speed up the returns for the report
you
> wanted. Next we need to populate our new table with the "spam" report
data
> (filled in from most spam to least spam per domain per day).
>
> INSERT spam_stats (report_date, report_sender_domain_id, processed,
spam,
> suspected)
> SELECT date(`time`)
> ,report_sender_domain_id
> ,sum(processed) as processed
> ,sum(spam) as spam
> ,sum(suspected) as suspected
> FROM report1
> GROUP BY date(`time`)
> ,report_sender_domain_id
> ORDER BY spam desc
> ,report_sender_domain_id
> ,date(`time`);
>
> What will happen is that the rank column will be automatically filled in
> for each day's spam statistics. Then to get your "top ten" list you just
> run:
>
> SELECT *
> FROM spam_stats
> WHERE rank <= 10;
>
> Update spam_stats at the end of each new day and will only need to
> calculate those values once. If you create more tables like spam_stats
for
> your other statistics (processed, suspected, blocked, etc.), you will be
> able to do rank-based reporting for those statistics, too ("top 20 sites
> blocked", for example).
>
> If you play around with the general idea of the groupwize numbering
> feature of auto_increment + primary key, I am sure you can solve a lot
of
> your reporting issues.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> Yahoo! Mail
> Stay connected, organized, and protected. Take the tour
