Shouldn't we always use Web Optimized for Handbrake?

Question

Update!

In my BigQuery, I've a table LAPS (simplified version) as below with sample data:

Timestamp               |   QRcode Read

2021-09-22 10:34:14.574 |   ID 10

2021-09-22 10:34:16.936 |   ID 11

2021-09-22 10:34:41.198 |   ID 12

2021-09-22 10:34:45.398 |   ID 19 ***---- preceding row for ID 19

2021-09-22 10:34:50.169 |   ID 14

2021-09-22 10:34:52.702 |   ID 15

2021-09-22 10:34:55.288 |   ID 16

2021-09-22 10:34:57.949 |   ID 17

2021-09-22 10:35:20.596 |   ID 20 <<<---- preceding row for ID 20

2021-09-22 10:35:25.659 |   ID 19 ***---- current row for ID 19

2021-09-22 10:35:30.127 |   ID 20 <<<---- current row for ID 20

Based on this, I'm creating a BigQuery View with one more column added called "Preceding Timestamp". The value in that column for each row should be the timestamp value immediately preceding the current row's timestamp if the ID is same.

In the example above, please note the last row (ID 20): The "Preceding Timestamp" value for that row will be the timestamp from the preceding row as indicated in the sample data. Similarly for ID 19.

Likewise, I want to find out the preceding timestamp for all the rows in that new BiQuery View.

The columns for new View are: Timestamp | ID | Preceding Timestamp

This View's pseudo query will be:

Select Laps.Timestamp, Laps.ID, Preceding_Timestamp where XYZ??? (I don't know how to do this!)

Older post:

I can take a solution for either Google Spreadsheet, Google Big Query or Google Datastudio whichever works best.

Please see the picture below: Google Spreadsheet screenshot with formula

We scan the student QR codes via mobile app that appear here with the scan timestamp as we go. Each scan represents one lap around the ground. We sometimes scan one QR code multiple times by mistake. My goal is to identify such duplicate scans and omit them to calculate the #laps/student accurately.

For that, as you can see in the formula, I followed following logic (it’s incomplete though):

1. For each row, filter all the previous scans by this row’s date (day, month, year) and by matching ID of this row
2. Sort it in reverse order and pick the top entry with SortN function. This gives the timestamp of preceding scan
3. Next I was going to select that timestamp entry (which I don't know how to. I was thinking VLookup?) and calculate the duration between current row’s timestamp minus the one we just got in previous steps.

Google Spreadsheet solution:

Following the previous logic, I will get the column R as Duration but I’ll have to repeat the formula for each row. I instead wanted to use ArrayFormula. Appreciate if you could help there.

Google Datastudio or Google BigQuery solution:

Little big of background here. My data flows like this: Mobile scan --> Google Spreadsheet --> Google Big Query --> Google DataStudio (the user-facing dashboard)

Ultimately my goal is to provide a Duration slider or some control to our users so they get a choice to select how much duration is still ok / invalid.

For this purpose, with my limited knowledge, I was thinking of adding a slider control on DataStudio UI that interacts with the Parameter which will talk to the Calculated Variable of Duration. Is it possible? How can I do that? I tried the logic that I did above with Spreadsheet but found very difficult to implement on Google DataStudio.

Another option I was thinking was to do it via BigQuery where I’ll run some form of following query but don’t know how to do nested Select and calculations in the Where clause. Also please suggest if any alternative is better.

SELECT Timestamp Where Timestamp Is NOT NULL AND (Select previous timestamps and do the Duration calculations and if Duration >= 3mins)

Solution 1

If the video is going to be streamed, ever, then you should web-optimize it. This places a special block of the video file at the front called an "moov atom", which is sort of like a table-of-contents. Not sure why, but it is not at the beginning by default, and can appear anywhere. For Html streaming, this block has to be read first before the remote user can start viewing the video.

For a great medium-level explanation on this see:
http://rigor.com/blog/2016/01/optimizing-mp4-video-for-fast-streaming

Solution 2

This is one of the reasons why not:

If you enable this and convert your video, the iPad will not play the video file! Instead you get the error "The operation could not be completed".

https://stackoverflow.com/q/24042894/1066234

Solution 3

I guess this comes down to opinion. I always select Web Optimized but that doesn't mean that everyone would want to do that. Handbrake is open source software and I've discovered that it likes to let people pick what they want.

If you don't have a need for the web optimized additions, then why include them in the encode?

Comments

[Mr.Batra]

Attach sample data (not image), it would be easier to help. This can be done easily via window functions.

[Guss]

Hi Sir, Please see the spreadsheet here. Please go to ROW 496 directly. docs.google.com/spreadsheets/d/… Thank you for your help!

[Guss]

Someone please reply? Thanks much!

[Mr.Batra]

add the complete logic that you want. Add a sample to this question, no spreadsheets please. Add expected output after applying the logic.

[Guss]

Ok! Let me try a different way! And I see you're tagged as BigQuery expert so will try to speak that language! Updated the problem above!

[GeekAbhiGeek]

no, whether the frame index data is in front or at the end of file, it really doesn't matter, does it?

[tbenz9]

I'm not sure if non web-optimized videos even have frame index data. Do you have a source that lists the inclusion of frame index data? Thanks.

[GeekAbhiGeek]

it is the fact that if the file is in your hard drive or is totally uploaded to the webpage, then you can jump to any point of the video. So there should be frame index data. Because, if there is no such data, then even if 20MB of the file is uploaded, then you should be able to watch the video from the start or even jump to 10 seconds later, shouldn't you? The fact that you can't means that there is some missing data that is crucial to the playing of the video even from the very start.

[GeekAbhiGeek]

so what I mean is, if the final file sizes are the same, and there is no penalty for quality whether it is Web-optimized or non-Web-optimized, then why not just make it Web-optimized all the time, so that the file is more universally usable?

[GeekAbhiGeek]

are you sure? I have tried both Web Optimized or not, and both cases, I can upload to the Movies app on iPad, or view it using Google Chrome on iPad if the file is served on my website by a web hosting company

[Avatar]

If you try to play the video files with a flash player they will only start when they go fully loaded (ipad conversion profile). Here you need to user "web optimized" then they start right away. If you play the videos with <video> HTML5 and they are web-optimized, then you jump forward in the timeline, the video loading will not work or waits until the video is loaded until this point.

[Guss]

Hi Sir, Sincerely appreciate it! And looking at the output, that's exactly I would like to have! Only problem is my input data is not static and my Laps table is growing almost everyday. From what I see with the "Select cast" statements above, I've to manually add data there. Right? Do we have any dynamic solution? Again Thank you so much for your help!

[Mr.Batra]

Do you have data in Big Query ? If not, then you will have to import it into BQ.

[Guss]

Yes Sir! I've the data (Timestamp and ID) in a BigQuery (console.cloud.google.com/bigquery) Table called Laps and around 500 new rows are getting programmatically added every week to it. In this case, I can't hard code the values in the query above. Appreciate if you could please provide an easy programmatic way to refer to the Laps table. Thanks much!

[Mr.Batra]

ok,good . This query is just added as sample. You can remove cast() and directly use your table , like this ---------select * ,lag(your_table_Timestamp_col) over (partition by id order by your_table_Timestamp_col) as preceeding_timestamp from your_table--------

[Guss]

Wow Sir! This actually worked like a charm! Thank you very much for this. I've struggled a lot to get this done so very happy! :) Only observation I saw is as below which I think will be a quick fix for you! Please note that for the 2nd row dated Sept 24th, it picked up Sept 22nd's date as previous timestamp. How do we limit it to that particular day only? So 2nd row will have null as because previous one is from 2 days earlier and not from that same day. Tx 2021-09-22 20:18:52.068 UTC | ID05 | 2021-09-22 20:16:02.017 UTC 2021-09-24 17:33:34.838 UTC | ID05 | 2021-09-22 20:18:52.068 UTC

[Guss]

I think I got it! All I did is added Date function in the Partition. (partition by ID, Date(Timestamp) order by Timestamp). It seems like it's working. Please let me know if this is right. Also would you advice using Timezone in the Date function? Don't know if it's useful in anyway here. A BIG THANK YOU again Sir!! I voted UP but since it's my 1st post they won't count it yet :(

[Mr.Batra]

There is a Datetime() function too. Try using it. For eg. ----select datetime("2021-09-22 20:18:52.068")---. And Welcome :)