Average per day

[christa]

hi friends, my db table is this one:

 

  `ID_product` int(1)

  `user_id` int(1)

  `datapub` int(10) (as Timestamp, eg: 1302766366)

  PRIMARY KEY (`id`)

 

I need to retrieve the daily average of ID_product inserted where user_id=someone

 

I've seen the AVG() function but I have problem with date!

 

 

 

 

[kickstart]

Hi

 

First you need a count of all the records by day per user. From that you can get the average.

 

Something like this:-

 

SELECT user_id, AVG(ProductCount)
FROM (SELECT user_id, DATE(datapub) AS DatePublished, COUNT(ID_product) AS ProductCount
FROM datetest
GROUP BY user_id, DatePublished) subselect
GROUP BY user_id

 

All the best

 

Keith

[christa]

your query returns:

 

user_id AVG(ProductCount)

1         25.5000

2         6.0000

 

The result is unreal because the user_di 1 has inserted 51 products and user_id 2 has inserted 6 products, both in the past 2 years

 

[kickstart]

Hi

 

Can you export the data so I can have a bit more of a play.

 

All the best

 

Keith

[kickstart]

Hi

 

Just thought, the average I have given is the average per used day. So user_id 1 has inserted 51 records in the last 2 years but on 2 days, user_id 2 has inserted 6 records but only done that on 1 day.

 

Do you need that kind of average, or an average over the elapsed days?

 

All the best

 

Keith

[kickstart]

Hi

 

This will give you the average per elapsed day (ie, number of days between the first and last inserted records for a particular user id).

 

SELECT user_id, ProductTotalCount / (DATEDIFF(MaxDatePublished,MinDatePublished) +1)
FROM (SELECT user_id, MIN(DatePublished) AS MinDatePublished, MAX(DatePublished) AS MaxDatePublished, SUM(ProductCount) AS ProductTotalCount
FROM (SELECT user_id, DATE(datapub) AS DatePublished, COUNT(ID_product) AS ProductCount
FROM datetest
GROUP BY user_id, DatePublished) subselect
GROUP BY user_id) outersubselect

 

If you want it to be between the first inserted date per user id and today then you can use todays date rather than the Max date.

 

All the best

 

Keith

[christa]

this is the dump and some values:

 

CREATE TABLE IF NOT EXISTS `products` (
  `ID_product` int(1) NOT NULL AUTO_INCREMENT,
  `user_id` int(1) NOT NULL,
  `data_pub` int(10) NOT NULL,
  PRIMARY KEY (`ID_product`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=59 ;

INSERT INTO `products` (`ID_product`, `user_id`, `data_pub`) VALUES
(1, 1, 1034145512),
(2, 1, 1047023998),
(3, 1, 1048863029),
(4, 1, 1082878114),
(5, 1, 1057765362),
(6, 1, 1069845339),
(7, 1, 1072974569),
(8, 1, 1073734879),
(9, 1, 1078061830),
(10, 1, 1081612558),
(11, 1, 1088926719),
(12, 1, 1097613613),
(13, 1, 1294484457),
(14, 1, 1108819740),
(15, 1, 1110113708),
(16, 1, 1113055990),
(17, 1, 1116447075),
(18, 1, 1121891159),
(19, 1, 1125754462),
(21, 1, 1129987058),
(22, 1, 1137935681),
(23, 1, 1147014380),
(24, 1, 1152644776),
(25, 1, 1153058150),
(26, 1, 1166644581),
(27, 1, 1171662130),
(28, 1, 1174755781),
(29, 1, 1190465853),
(30, 1, 1198401192),
(31, 1, 1212938489),
(32, 1, 1233401000),
(33, 1, 1241865261),
(34, 1, 1271504408),
(35, 1, 1296487128),
(36, 1, 1296570001),
(37, 1, 1296573545),
(38, 1, 1296579164),
(39, 1, 1296585392),
(40, 1, 1296585421),
(41, 1, 1296585455),
(42, 1, 1296585648),
(43, 1, 1296815811),
(44, 1, 1297454728),
(45, 1, 1297454975),
(46, 1, 1298120648),
(47, 1, 1298807272),
(48, 1, 1299010216),
(49, 1, 1299092477),
(50, 1, 1299180670),
(51, 1, 1299404673),
(52, 2, 1301249170),
(53, 2, 1301252055),
(54, 2, 1301913083),
(55, 2, 1302523995),
(56, 2, 1302526008),
(57, 2, 1302695466),
(58, 2, 1302766366);

 

also the your second query returns unexpected results

[kickstart]

Hi

 

Using that data try this ( changed as you are using unix timestamps, last one worked on date / time fields):-

 

SELECT user_id, ProductTotalCount / ( DATEDIFF( MaxDatePublished, MinDatePublished ) +1 )
FROM (SELECT user_id, MIN( DatePublished ) AS MinDatePublished, MAX( DatePublished ) AS MaxDatePublished, SUM( ProductCount ) AS ProductTotalCount
FROM (SELECT user_id, DATE( FROM_UNIXTIME( data_pub ) ) AS DatePublished, COUNT( ID_product ) AS ProductCount
FROM products
GROUP BY user_id, DatePublished)subselect
GROUP BY user_id) outersubselect

 

That gives an average of 0.0163 for user_id 1 and 0.3684. First one has inserted 50 records between 2002-10-09 and 2011-03-06. Second has inserted 7 records between 2011-03-27 and 2011-04-14.

 

All the best

 

Keith

[christa]

thanks, it seems to work fine.

I explain: my goal is to obtain a result as in this forum:

 

kickstart Posts: 3.122 per day

Christa Posts: 0.049 per day

and so on...

[kickstart]

Hi

 

In which case you probably want the number of days from their first post until today.

 

SELECT user_id, ProductTotalCount / ( DATEDIFF( NOW( ) , MinDatePublished ) +1 )
FROM (SELECT user_id, MIN( DatePublished ) AS MinDatePublished, SUM( ProductCount ) AS ProductTotalCount
FROM (SELECT user_id, DATE( FROM_UNIXTIME( data_pub ) ) AS DatePublished, COUNT( ID_product ) AS ProductCount
FROM products
GROUP BY user_id, DatePublished)subselect
GROUP BY user_id)outersubselect

 

Simplified a bit (but a bit dubious about mucking around with aggregate functions in the SELECT like this):-

 

SELECT user_id, COUNT( ID_product ) / (DATEDIFF( NOW( ) ,MIN( FROM_UNIXTIME( data_pub ) ))+1) AS AverageRecs
FROM products
GROUP BY user_id

 

All the best

 

Keith

[christa]

good. this is exactly what I wanted, many thanks!