CODEWITHSUNDEEP
sqlite
user1720580
user1720580

Reputation:

Sorting, ordering and creating columns in SQLite

I'm a student doing a project and I have to use SQLite however i'm new to SQLite. I have described my problem below.

I have a table(containing 60000 rows) which is of the form shown below.

Date_Time           Country_City Temperature
4-10-2012 12:10:10  USA_NYC        20
4-10-2012 12:10:10  USA_LA         17
4-10-2012 12:10:10  USA_DC         19
4-10-2012 12:40:10  USA_LA         18
4-10-2012 12:40:10  USA_DC         18

I have to sort and create the table as shown below, if the data is missing as in NYC the previous data must be loaded if not 'NaN'. 

Date_Time         USA_NYC USA_LA USA_DC
4-10-2012 12:10:10 20      17     19
4-10-2012 12:40:10 20      18     18

Can you please tell me how to create it, I could order and sort, but I do not know how to create like this in SQLite. I would be grateful for any suggestions.

Upvotes: 2

Views: 145

Answers (2)

user1720580
user1720580

Reputation:

@Cl: Thank you for your clarification. the answer for the exercise is

SELECT timestamps.Date_Time,

   (SELECT Temperature
    FROM mytable tvalues
    WHERE tvalues.Country_City = 'USA_NYC'
      AND tvalues.Date_Time LIKE substr(timestamps.Date_Time, 1, 10) || '%'
      AND tvalues.Date_Time <= timestamps.Date_Time
      AND tvalues.Temperature IS NOT NULL
    ORDER BY tvalues.Date_Time DESC
    LIMIT 1) AS USA_NYC, 
   (SELECT Temperature FROM mytable tvalues WHERE tvalues.Country_City = 'USA_LA'
      AND tvalues.Date_Time LIKE substr(timestamps.Date_Time, 1, 10) || '%'
      AND tvalues.Date_Time <= timestamps.Date_Time
      AND tvalues.Temperature IS NOT NULL
    ORDER BY tvalues.Date_Time DESC
    LIMIT 1) AS USA_LA,     SELECT Temperature
    FROM mytable tvalues
    WHERE tvalues.Country_City = 'USA_DC'
      AND tvalues.Date_Time LIKE substr(timestamps.Date_Time, 1, 10) || '%'
      AND tvalues.Date_Time <= timestamps.Date_Time
      AND tvalues.Temperature IS NOT NULL
    ORDER BY tvalues.Date_Time DESC
    LIMIT 1) AS USA_DC,

FROM (SELECT DISTINCT Date_Time FROM mytable) timestamps

is this the required solution or else where u expecting anything different which will be faster?

Upvotes: 0

CL.
CL.

Reputation: 180070

For a given timestamp and city, the following query gets the latest temperature at or before that time:

SELECT Temperature AS USA_NYC
FROM mytable
WHERE Country_City = 'USA_NYC'
  AND Date_Time LIKE '4-10-2012 %'
  AND Date_Time <= '4-10-2012 12:40:10'
  AND Temperature IS NOT NULL
ORDER BY Date_Time DESC
LIMIT 1

(Please note that the Date_Time comparisons could be simplified if you used one of SQLite's supported date/time formats.)

We also get all the timestamps for which we want some temperature:

SELECT DISTINCT Date_Time FROM mytable

Now we combine these two queries to get the NYC value for each timestamp:

SELECT timestamps.Date_Time,
       (SELECT Temperature
        FROM mytable tvalues
        WHERE tvalues.Country_City = 'USA_NYC'
          AND tvalues.Date_Time LIKE substr(timestamps.Date_Time, 1, 10) || '%'
          AND tvalues.Date_Time <= timestamps.Date_Time
          AND tvalues.Temperature IS NOT NULL
        ORDER BY tvalues.Date_Time DESC
        LIMIT 1) AS USA_NYC
FROM (SELECT DISTINCT Date_Time
      FROM mytable) timestamps

Adding the columns for LA and DC is left as an exercise. (It is not possible to generate these columns from the Country_City values automatically; SQLite does not have pivot table functions.)

Upvotes: 1

Related Questions