A detective (aka investigator) was an agent who investigated crimes in order to solve them. This could be done on behalf of a government or as one's private occupation. Someone who did it privately was known as a criminal investigator, private consulting investigator, private investigator, private eye, or P.I.
In the Earth movie Appointment with Danger, seen in T'Pol's nightmare in 2153, a character was referred to by Doctor Phlox as "the detective in the bow tie." He believed that the man hadn't really died in a house fire and would be seen later. (ENT: "Impulse")
Dixon Hill was a fictional 20th century Earth detective. Captain Jean-Luc Picard was fond of Dixon Hill novels, and in his free time would often escape to the holodeck where he would assume the role of Hill in holographic re-creations of the detective's stories. (TNG: "The Big Goodbye", "Manhunt", "Clues"; Star Trek: First Contact)
Sherlock Holmes was a famous fictional Earth detective whom Data imitated on occasion. (TNG: "Lonely Among Us", "Elementary, Dear Data")
Preston was a detective with the SFPD in 2024. (DS9: "Past Tense, Part II")
In his capacity as constable aboard Deep Space 9, Odo performed many detective duties for Gul Dukat and later Commander Benjamin Sisko. (DS9: "Necessary Evil")
Miles O'Brien was fond of the Mike Hammer detective novels by Mickey Spillane, including I, the Jury and Kiss Me Deadly. (DS9: "Profit and Loss", "Shadows and Symbols") Despite this, O'Brien himself was an engineer, not a detective, as Captain Benjamin Sisko pointed out when O'Brien went missing after searching for the widow of Liam Bilby on New Sydney in 2375. (DS9: "Prodigal Daughter")