How should I write a SQL statement that satisfies the conditions for PI points per unit time? Or which table should I use to get this (like this: .. piserver.catalogs.piarchive..picomp2;or and so on)
depends on what you really want to achieve. Show all Tags that are within a specific time period or are you looking for a specific value which is a timestamp?
SELECT * FROM [piarchive]..[picomp2] WHERE tag like '%' AND time BETWEEN '*-1h' AND '*'
Option 2: SELECT * FROM [piarchive]..[picomp2] WHERE tag like '%' AND value = '2020-10-19 10:00:00'
Take a look at the Query Compendium in PI SQL Commander.
There you should find answers for more questions.
If you would like to get evenly spaced data, then you should utilize piinterp2 table. This is in short for Interpolated data. More detailed sample queries can be found in the Query Compendium as Sebastian Mróz mentioned.
I'll attach the online documentation line as PI OLEDB Provider - Archive Data Views
Retrieving data ...