Skip to main content

Benjamin Oakes

Photo of Ben Oakes

Hi, I'm Ben Oakes and this is my geek blog. Currently, I'm a Ruby/JavaScript Developer at Liaison. Previously, I was a Developer at Continuity and Hedgeye, a Research Assistant in the Early Social Cognition Lab at Yale University and a student at the University of Iowa. I also organize TechCorridor.io, ICRuby, OpenHack Iowa City, and previously organized NewHaven.rb. I have an amazing wife named Danielle Oakes.

Filtering for the month March, 2010. Clear

Is there a boolean literal in SQLite?

by Ben

From my question on StackOverflow (CC BY-SA 3.0):

I know about the boolean column type, but is there a boolean literal in SQLite? In other languages, this might be true or false. Obviously, I can use 0 and 1, but I tend to avoid so-called “magic numbers” where possible.

From this list, it seems like it might exist in other SQL implementations, but not SQLite. (I’m using SQLite 3.6.10, for what it’s worth.)

Calculating multiple column average in SQLite3

by Ben

From my question on StackOverflow (CC BY-SA 3.0):

I need to average some values in a row-wise fashion, rather than a column-wise fashion. (If I were doing a column-wise average, I could just use avg()). My specific application of this requires me ignore NULLs in averaging. It’s pretty straightforward logic, but seems awfully difficult to do in SQL. Is there an elegant way of doing my calculation?

I’m using SQLite3, for what it’s worth.

Details

If you need more details, here’s an illustration:

I have a a table with a survey:

| q1 | q2    | q3    | ... | q144 |
|----|-------|-------|-----|------|
| 1  | 3     | 7     | ... | 2    |
| 4  | 2     | NULL  | ... | 1    |
| 5  | NULL  | 2     | ... | 3    |

(Those are just some example values and simple column names. The valid values are 1 through 7 and NULL.)

I need to calculate some averages like so:

q7 + q33 + q38 + q40 + ... + q119 / 11 as domain_score_1
q10 + q11 + q34 + q35 + ... + q140 / 13 as domain_score_2
...
q2 + q5 + q13 + q25 + ... + q122 / 12 as domain_score_14

…but i need to pull out the nulls and average based on the non-nulls. So, for domain_score_1 (which has 11 items), I would need to do:

Input:  3, 5, NULL, 7, 2, NULL, 3, 1, 5, NULL, 1

(3 + 5 + 7 + 2 + 3 + 1 + 5 + 1) / (11 - 3)
27 / 8
3.375

A simple algorithm I’m considering is:

Input:

3, 5, NULL, 7, 2, NULL, 3, 1, 5, NULL, 1 

Coalesce each value to 0 if NULL:

3, 5, 0, 7, 2, 0, 3, 1, 5, 0, 1

Sum:

27

Get the number of non-zeros by converting values > 0 to 1 and sum:

3, 5, 0, 7, 2, 0, 3, 1, 5, 0, 1
1, 1, 0, 1, 1, 0, 1, 1, 1, 0, 1
8

Divide those two numbers

27 / 8
3.375

But that seems like a lot more programming than this should take. Is there an elegant way of doing this that I’m not aware of?

Plot inverse colors in Matlab?

by Ben

From my question on StackOverflow (CC BY-SA 3.0):

I’m plotting on top of an image in Matlab. Sometimes, I can’t see what’s being plotted because the color of the image underneath is too close to the color of the image at the same location. I could just always change the color of the plot (e.g. from ‘rx’ to ‘bx’), but that’s cumbersome.

Is it possible to plot the inverse color of what’s underneath so that the overlay is always visible?