Asked  7 Months ago    Answers:  5   Viewed   33 times

I'm building a quick csv from a mysql table with a query like:

select DATE(date),count(date) from table group by DATE(date) order by date asc;

and just dumping them to a file in perl over a:

while(my($date,$sum) = $sth->fetchrow) {
    print CSV "$date,$sumn"
}

There are date gaps in the data, though:

| 2008-08-05 |           4 | 
| 2008-08-07 |          23 | 

I would like to pad the data to fill in the missing days with zero-count entries to end up with:

| 2008-08-05 |           4 | 
| 2008-08-06 |           0 | 
| 2008-08-07 |          23 | 

I slapped together a really awkward (and almost certainly buggy) workaround with an array of days-per-month and some math, but there has to be something more straightforward either on the mysql or perl side.

Any genius ideas/slaps in the face for why me am being so dumb?


I ended up going with a stored procedure which generated a temp table for the date range in question for a couple of reasons:

  • I know the date range I'll be looking for every time
  • The server in question unfortunately was not one that I can install perl modules on atm, and the state of it was decrepit enough that it didn't have anything remotely Date::-y installed

The perl Date/DateTime-iterating answers were also very good, I wish I could select multiple answers!

 Answers

46

When you need something like that on server side, you usually create a table which contains all possible dates between two points in time, and then left join this table with query results. Something like this:

create procedure sp1(d1 date, d2 date)
  declare d datetime;

  create temporary table foo (d date not null);

  set d = d1
  while d <= d2 do
    insert into foo (d) values (d)
    set d = date_add(d, interval 1 day)
  end while

  select foo.d, count(date)
  from foo left join table on foo.d = table.date
  group by foo.d order by foo.d asc;

  drop temporary table foo;
end procedure

In this particular case it would be better to put a little check on the client side, if current date is not previos+1, put some addition strings.

Tuesday, June 1, 2021
 
exxed
answered 7 Months ago
20

You mean like this?

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), dob)), "%Y")+0 AS age from sometable

(Source)

Wednesday, August 4, 2021
 
Rob13
answered 4 Months ago
48

The best way is to make sure that you run SELECT COUNT on a single column (SELECT COUNT(*) is slower) - but SELECT COUNT will always be the fastest way to get a count of things (the database optimizes the query internally).

If you check out the comments below, you can see arguments for why SELECT COUNT(1) is probably your best option.

Thursday, August 12, 2021
 
ajjumma
answered 4 Months ago
22

There are two approaches. You can insert (?, ?, ?) a number of times based on the size of the array. The text manipulation would be something like:

my $sql_values = join( ' ', ('(?, ?, ?)') x scalar(@array) );

Then flatten the array for calling execute(). I would avoid this way because of the thorny string and array manipulation that needs to be done.

The other way is to begin a transaction, then run a single insert statement multiple times.

my $sql = 'INSERT INTO tbl (col1, col2, col3)';
$dbh->{AutoCommit} = 0;
my $sth = $dbh->prepare_cached( $sql );
$sth->execute( @$_ ) for @array;
$sth->finish;
$dbh->{AutoCommit} = 1;

This is a bit slower than the first method, but it still avoids reparsing the statement. It also avoids the subtle manipulations of the first solution, while still being atomic and allowing disk I/O to be optimized.

Friday, August 13, 2021
 
user729076
answered 4 Months ago
53

Lift JSON provides several different styles of deserializing JSON. Each have their pros and cons.

val json = JsonParser.parse(""" { "foo": { "bar": 10 }} """)

LINQ style query comprehension:

scala> for { JField("bar", JInt(x)) <- json } yield x 

res0: List[BigInt] = List(10)

More examples: http://github.com/lift/lift/blob/master/framework/lift-base/lift-json/src/test/scala/net/liftweb/json/QueryExamples.scala

Extract values with case classes

implicit val formats = net.liftweb.json.DefaultFormats 
case class Foo(foo: Bar) 
case class Bar(bar: Int) 
json.extract[Foo] 

More examples: https://github.com/lift/lift/blob/master/framework/lift-base/lift-json/src/test/scala/net/liftweb/json/ExtractionExamples.scala

XPath style

scala> val JInt(x) = json  "foo"  "bar"

x: BigInt = 10

Non-type safe values

scala> json.values

res0: Map((foo,Map(bar -> 10)))
Monday, September 6, 2021
 
Reid
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share