network profile
| bio | website | |
|---|---|---|
| location | ||
| age | ||
| visits | member for | 10 months |
| seen | Jul 31 '12 at 17:02 | |
| stats | profile views | 1 |
|
Jul 31 |
comment |
MySQL: AVG of defined variable per monthSELECT 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 monthSELECT 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 |
