13 reputation
4
bio website
location
age
visits member for 1 year, 9 months
seen Jul 31 '12 at 17:02

Jul
31
comment MySQL: AVG of defined variable per month
SELECT DISTINCT userid, count( userid ) as login_count FROM (SELECT DISTINCT userid, date( FROM_UNIXTIME( date_time ) ) AS DAY FROM xcart_login_history WHERE status="success" and (action ="login" or action = "autologin") ORDER BY userid, DAY) as login_days WHERE login_days.DAY < ( SELECT DISTINCT min(date(FROM_UNIXTIME(xcart_orders.date))) FROM xcart_orders WHERE xcart_orders.userid = login_days.userid GROUP BY userid ) GROUP BY userid That is the old code. Now I need min(date(FROM_UNIXTIME(xcart_login_history.date_time))) and min(date(FROM_UNIXTIME(xcart_orders.date))). Doesn't work :(
Jul
31
comment MySQL: AVG of defined variable per month
@JonofAllTrades: thanks for the link! I read it and tried a bit around but I think I will try a different approach which is probably more appropriate for a beginner like me. I will use the old query to show me distinct userids and their number of logins before their first buy. Then to those two aspects I would like to add the minimum logindate (registerdate) and the minimum buydate (firstbuy) per distinct userid. And that's were I am stuck right now. My goal is to then export this table with those 4 columns to excel and work on it there
Jul
31
awarded  Scholar
Jul
31
accepted MySQL: AVG of defined variable per month
Jul
31
comment MySQL: AVG of defined variable per month
SELECT COUNT(xlg.userid) as logincount_KW1 FROM xcart_login_history as xlg, xcart_orders as xo WHERE xlg.userid=xo.userid AND min(date(FROM_UNIXTIME(xlg.date_time))) BETWEEN '02-01-2012' AND '08-01-2012' AND xlg.status`="success" AND (xlg.action="login" or xlg.action="autologin") AND min(date(FROM_UNIXTIME(xo.date))) BETWEEN '05-03-2012' AND '11-03-2012'` Until now it tells me I am executing "invalid use of group function". :/
Jul
31
comment MySQL: AVG of defined variable per month
@JonofAllTrades: Thanks for investing your time. That really is huge help for me. Thank you a lot. I am just working on another query, which (if you have the time) I could need some help with. I want to display something like a Matrix. This is how it should look: on the horizontal I would like to display the weeknumbers for 2012 in terms of first(!) login [min(date(FROM_UNIXTIME(xlg.date_time)))]. In the vertical sense I want to display weeknumber referring to the first buy [(min(date(FROM_UNIXTIME(xo.date)))]. The content should display the AVG logincount. Here is my try:
Jul
27
awarded  Editor
Jul
26
awarded  Commentator
Jul
26
comment MySQL: AVG of defined variable per month
@JonofAllTrades: That is exactly 1:1 what I am looking for. Can't tell you how thankful I am for your help. Thanks a lot!
Jul
26
awarded  Student
Jul
26
comment MySQL: AVG of defined variable per month
Sorry for gabbering so much, but what I am actually missing is a appropriate function which I can use as a filter to only count logins between the buydates :/
Jul
26
comment MySQL: AVG of defined variable per month
Now here is the problem: The AVG's rise constantly from month to month. This basis on the fact that the query calculates as following: it does not count the login BETWEEN buys; it rather counts all logins from t0 to buy1,2,3,4 and then calculates an average. This is the reason for the growing average. So how do I change the code to actually count the logins BETWEEN buys and then builds an average? :(. Thanks so much
Jul
26
comment MySQL: AVG of defined variable per month
@JonofAllTrades: Need help on this one. This is the actual code `SELECT Month, AVG(PreviousLogins) AS AvgPrevLogins FROM ( SELECT MONTH(FROM_UNIXTIME(O.date)) AS Month, COUNT(L.userid) AS PreviousLogins FROM xcart_orders AS O JOIN xcart_login_history AS L ON O.userid = L.userid AND L.date_time < O.date AND L.status = 'success' AND L.action IN ('login', 'autologin') AND YEAR(FROM_UNIXTIME(O.date))=2012 AND MONTH(FROM_UNIXTIME(O.date))=1 AND MONTH(FROM_UNIXTIME(L.date_time))=1 GROUP BY MONTH(FROM_UNIXTIME(O.date)), O.orderid ) AS X GROUP BY Month ORDER BY Month
Jul
26
comment MySQL: AVG of defined variable per month
Sorry, I couldn't figure out how to post the query as nice as you did above. Now this query actually gives me "realistic" numbers for the logins before order, for the orders placed in January 2012. However, if I run the query without the MONTH(FROM_UNIXTIME(O.date))=1 line it shows me the logins before order per month but they increase slightly every month. So my guess is that the query includes all previous logins when calculating for the next month. Sorry, I hope you can understand my newbie gabbering.
Jul
26
comment MySQL: AVG of defined variable per month
@JonofAllTrades: I've been trying around and now came up with this code: <br/> SELECT Month, AVG(PreviousLogins + 0.0) AS AvgPrevLogins FROM ( SELECT MONTH(FROM_UNIXTIME(O.date)) AS Month, COUNT(L.userid) AS PreviousLogins FROM xcart_orders AS O JOIN xcart_login_history AS L ON O.userid = L.userid AND L.date_time < O.date AND L.status = 'success' AND L.action IN ('login', 'autologin') AND YEAR(FROM_UNIXTIME(O.date))=2012 AND MONTH(FROM_UNIXTIME(O.date))=1 GROUP BY MONTH(FROM_UNIXTIME(O.date)), O.orderid ) AS X GROUP BY Month ORDER BY Month
Jul
18
comment MySQL: AVG of defined variable per month
@JonofAllTrades: I just ran the query on a subset of 50 different userids. However, the average_logins are way to low (max is 0,05060). I would expect values ranging from 10-20 per month. Do you have an idea why this could be? I'm continuing to look into it
Jul
18
comment MySQL: AVG of defined variable per month
Ok, but that's perfect for my intentions. I am creating a subset of the login_history table since I think my machine is not capable of running the query on such a large table. I will keep you posted if it works. Thanks a lot for your support so far!
Jul
18
comment MySQL: AVG of defined variable per month
Hi Jon, thanks a lot for your input. This seems like what I was trying to do. However, I receive an error referring to the CAST(PreviousLogins AS REAL) statement. Now I am running the Query without the statement. It's been taking about an hour, but I guess with 7 million columns that is no suprise.
Jul
17
comment MySQL: AVG of defined variable per month
Thank you guys for your help. Let me try to explain: I want to find the average number of logins a customer has executed before actually buying something. Here an example of a row in the xcart_login_history: userid=1 ; date_time=1304006969; action=login; status=success Here an example of a row in the xcart_orders: userid=1 ; date=1329855906; These are actually all of the relevant information (dates are all UNIXTIME). What I want to see as an endresult: Month=January ; AVG_logins_before_buy= 17 Month=February ; AVG_logins_before_buy= 14 and so on.
Jul
13
asked MySQL: AVG of defined variable per month