Those queries are definitely convenient early on but eventually you shouldn't be making those against that system and instead aggregate the data into warehouse.

Technically, there are DBs that let you do cross shard queries. See Azure Elastic DB.