Sometimes you want to do things with your Web application that isn't directly part of it. Like modifying requests or responses or caching. And sometimes you want to do this and prevent clients from accessing these modifying code.
A service worker is mostly transparent. It can for example intercept a
fetch event and modify it somehow. Setting different headers, getting some of the content from a cache or different servers, etc.
But they run on the client.
Cloudflare Workers run on the Edge of the Cloudflare infrastructure, right between your clients and your servers, but they have the same API as Service Workers.
This means, you can write a service Worker that runs on your dev client, but won't run on your users clients, because you have a clean interface between the worker and the app.
This can be used to write static front-ends and route the requests through the Worker, for example to add API secrets that would normally need to set up a whole server. Since it runs on the Edge the code is in geographical proximity to the client and not the server, which reduces latency.
Cloudflare Workers seem to be a nice alternative to solutions like AWS Lambda. While Lambda@Edge is a similar offering, the Workers adhere to the Service Worker standard, which could bring benefits on porting it to another provider or moving code between client and Edge when needed.
All in all, they give more power to the front-end and allow for less infrastructure.