2

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.

Improve this question
1
  • 1
    What if they're all missing for a half hour? Commented Oct 6, 2012 at 11:57

2 Answers 2

Reset to default
1

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.)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank You for your solution,it works perfectly.I have another query, if I have 800 different variables(similar to Country_city:USA_NYC,USA_LA,CAN_Tor.) so should I define for all the 800 variables. i hope its not and what is the solution for it?
You mean you want to have a table with 800 columns? As I wrote at the end of the answer, this is not possible. You would have to return the data in normalized form, as in the original table.
0

@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?

Improve this answer

8 Comments

@Cl: Thank you for your clarification. the answer for the exercise is shown above, is this the required solution or else where u expecting anything different?
Yes, this is what you'd use for three cities. This would be insane to do for 800 cities; do you really need them as separate columns?
@CL: Yes. For some Columns i dont want to call the past values instead NULL or NaN must be displayed what change should i do?
Getting NULL would be a separate question. Do you really need a query with 800 columns, or do you just need to display a 800-column table? (And how would you display it?)
@Cl. I just want to sort and create columns like USA_NYC,USA_IL, etc., as many cities there are in it, (generally there are 800 cities in one db), if its not available then it shouldn't display.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.