24 April 2012

The Conflicted Data Analyst

Inspired by a post from Juice Analytics.

We are a conflicted people. We love our TV and movie violence but worry that it ruins our children’s minds. We want to reduce healthcare costs, but don’t want to restrict the free market.

Conflicts like these leave little room for a satisfactory answer. Basic principles are in conflict and deeply-rooted desires run up against painful consequences. We don’t want to choose, and the middle ground feels like failure.

The people who practice data analysis and visualization have their own set of conflicts. Does this sound familiar?

“I want a feature-rich analytics tool, but…
My powerful analytics tool is too complex to use.”

“I want a dashboard that summarizes my business or functional area, but…
I don’t want to feel limited in what information I get to see.”

“I want a predictive model, but…
I don’t trust black box models when a result doesn’t align with my understanding.”

“I only want to focus on actionable data, but…
hey, look at that data point…that’s kind of interesting.”

“I want this report immediately, but…
now that I’ve got it, I don’t really have time to look at it.”

“I want to access data without a technical resource, but…
I don’t want to learn SQL because that’s for technical people.”

“I want to run scenario analyses on my data to make better decisions, but… I don’t believe the analysis can account for the complexity of my business.”

“I love geographic maps and network diagrams, but…
it is hard to see patterns or find insights from these types of visualizations.”

In a sense, each of these conflicts represents an opportunity to innovate to solve a persistent problem within our data analysis solutions. Find a way to satisfy the initial desire without suffering the assumed consequence, and you’ve got a chance to delight users of data.

24 January 2012

How to Find Out if an Entire String Is Numeric

Problem: Find out if an entire string is numeric.  (Just like the is_numeric function in php.)

Problem, Part II: Most online resources show how you can find out if part of your string is numeric.

Solution:
SELECT str FROM tbl
  WHERE str REGEXP('(^[0-9]+$)');
Give it a whirl!


For those who would like to view some test results, here goes:
mysql> SELECT str FROM tbl;
+-----+
| str |
+-----+
| 10  |
| 10A |
| 10E |
| 78  |
| E78 |
+-----+
5 rows in set (0.00 sec)
mysql> SELECT str FROM tbl WHERE str REGEXP('(^[0-9]+$)');
+-----+
| str |
+-----+
| 10  |
| 78  |
+-----+
2 rows in set (0.00 sec)
Now for the other way around:
mysql> SELECT str FROM tbl WHERE str NOT REGEXP('(^[0-9]+$)');
+-----+
| str |
+-----+
| 10A |
| 10E |
| E78 |
+-----+
3 rows in set (0.00 sec)