StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POfind standard deviation of days between valid verifications
text
Body
copied!<p>I have to calculate </p> <ol> <li>avg days between valid verification</li> <li>standard deviation of days between valid verification</li> </ol> <p>For example i have a table psverify_interaction </p> <pre><code>mysql> show create table psverify_interaction\G *************************** 1. row *************************** Table: psverify_interaction Create Table: CREATE TABLE `psverify_interaction` ( `interaction_id` int(11) NOT NULL AUTO_INCREMENT, `customer_id` int(11) NOT NULL, `time_stamp` datetime NOT NULL, `request_message_details` text, `verification_status` varchar(30) DEFAULT NULL, `latitutde` varchar(20) DEFAULT 'NA', `longitude` varchar(20) DEFAULT 'NA', `ip_address` varchar(45) DEFAULT 'NA', `batch_id` int(11) DEFAULT NULL, `interaction_channel` varchar(10) DEFAULT NULL, PRIMARY KEY (`interaction_id`), KEY `customer_id` (`customer_id`) ) ENGINE=MyISAM AUTO_INCREMENT=381845 DEFAULT CHARSET=latin1 1 row in set (0.02 sec) </code></pre> <p>Now I need to find for each customer</p> <ol> <li>avg days between valid verification</li> <li>standard deviation of days between valid verification</li> </ol> <p>For avg days between valid verification for each customer can do like this </p> <pre><code>SELECT customer_id, DATEDIFF(MAX(time_stamp),MIN(time_stamp)) / (COUNT(time_stamp)-1) as 'avg days between valid verification' FROM psverify_interaction AND verification_status = 'valid' GROUP BY customer_id; </code></pre> <p>Now i need to find "standard deviation of days between valid verification for each customer" , How can i find it in query</p> <p><strong>Example data</strong> </p> <pre><code>SELECT interaction_id,customer_id,time_stamp FROM psverify_interaction WHERE customer_id = 352 and verification_status = 'valid'; +----------------+-------------+---------------------+ | interaction_id | customer_id | time_stamp | +----------------+-------------+---------------------+ | 60809 | 352 | 2010-04-07 08:22:02 | | 88703 | 352 | 2010-05-11 02:08:02 | | 88888 | 352 | 2010-05-19 00:20:02 | | 88889 | 352 | 2010-05-19 00:28:07 | | 90024 | 352 | 2010-08-09 11:09:02 | +----------------+-------------+---------------------+ </code></pre> <p>So for this avg days between valid verification can be calculated like</p> <pre><code>SELECT customer_id, DATEDIFF(MAX(time_stamp),MIN(time_stamp)) / (COUNT(time_stamp)-1) FROM psverify_interaction WHERE customer_id = 352 AND verification_status = 'valid' GROUP BY customer_id; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload