0

My database table looks like table 1 below:

Table 1:
    +----------------------------------------------------------------------------------+
    |   id     | distance | duration_sec | speed | foot | bike | car | bus | metro     |
    | -----------+----------+--------------+-------+------+------+-----+-----+---------|
    | 461275000 |  2661.08 |          953 |  2.79 | f    | f    | f   | t   | f        |
    | 451918000 |  6289.75 |         1381 |  4.55 | f    | f    | t   | f   | f        |
    | 453044000 |  5938.43 |          950 |  6.25 | t    | f    | t   | f   | f        |
    | 445673000 |  2734.22 |         2333 |  1.17 | t    | f    | f   | f   | f        |
    | 456831000 |  7566.85 |         2071 |  3.65 | t    | f    | f   | t   | f        |
    | 443467000 |  1763.64 |          991 |  1.78 | t    | f    | t   | f   | f        |
    +----------------------------------------------------------------------------------+

I want to select into a new column mode, the column for which one of (foot, bike, car, bus, metro) is TRUE. For rows where foot and another mode are TRUE, I have to make a decision considering the speed. If the speed is above 2m/s, I consider it as another mode, else as foot.

The end result should be as in table 2 (decision preceded with # sign). How do I do this?

Table 2:
    +-------------------------------------------------------------------------------------------+
    |    id     | distance | duration_sec | speed | foot | bike | car | bus | metro | mode      |
    +------------+----------+--------------+-------+------+------+-----+-----+-------+--------- |
    | 461275000 |  2661.08 |          953 |  2.79 | f    | f    | f   | t   | f     |  bus      |
    | 451918000 |  6289.75 |         1381 |  4.55 | f    | f    | t   | f   | f     |  car      |
    | 453044000 |  5938.43 |          950 |  6.25 | t    | f    | t   | f   | f     |  #car     |
    | 445673000 |  2734.22 |         2333 |  1.17 | t    | f    | f   | f   | f     |  foot     |
    | 456831000 |  7566.85 |         2071 |  3.65 | t    | f    | f   | t   | f     |  #bus     |
    | 443467000 |  1763.64 |          991 |  1.78 | t    | f    | t   | f   | f     |  #foot    |
    +-------------------------------------------------------------------------------------------+

I need a way to decide mode is foot only when the entire trip is by foot, (not foot-> bus, foot -> car etc)

EDIT

Just to add details to my question. the columns foot, bike car bus and metro show travel mode used. Some trips used more than one mode e.g foot -> car. My goal is to select a single travel mode per row. I can, for instance, consider the distance travelled, if it's above a threshold (say 4km), this is typically done by non-foot mode, so I consider it as car mode as whole.

For cases when two other modes are true (say bus and metro), I want to completely ignore that row.

Foot mode should only be considered as foot when only foot is TRUE or very low speed for short distance below 2km

  • What is the priority? for example, if car and bus are true, what value must be set? the same when foot and 2 or more additional values are true and speed is above 2 m/s. In general - 1 long CASE WHEN or 2 nested. – Akina Dec 19 '19 at 13:55
  • For cases where for instance car and bus or bus and metro are true, I want to ignore the entire row as there are not many such cases. – arilwan Dec 19 '19 at 14:01
0

You want nested CASE statements. You haven't full described the problem, we can't give you a full answer. One branch would list 'foot' as the first priority, and other as the last priority (or maybe not consider it at all, or consider it an error) Something like:

CASE WHEN speed > 2 then
  CASE WHEN bike then 'bike'
  CASE WHEN car then 'car'
  ...
  CASE when foot then 'foot and/or error'
  ELSE 'none'
  END
ELSE
  CASE when foot then 'foot'
  CASE WHEN bike then 'bike'
  CASE WHEN car then 'car'
  ...
  ELSE 'none'
  END
END

You can't ignore a row from the select-list, so you probably need to write logic in two places. In the WHERE clause, to ignore rows with a true in more than one non-foot column, plus a CASE expression like the above in the select list, (or in the SET list if doing an UPDATE).

|improve this answer|||||
  • I edit my question to give some details. – arilwan Dec 19 '19 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.