Praxis has given an excellent answer to the latter part of the question, regarding the Sioux origin of the phrase.
With regard to the first part of the question, on for whom it is a good day to die:
In universe, the phrase may originate with tales of Kahless and Lukara. In DS9: Looking for par'Mach in All the Wrong Places, Worf and Jadzia are teaching Quark lines attributed to these two lovers-in-arms:
Lukara: MoVas ah-kee rustak. ("Today was a good day to tie.")
Kahless: Kosh tomah ehpaq Lukara kaVeir. ("The day is not yet over, Lukara.")
In this scene, Kahless and Lukara are fighting the forces of Molor at the Great Hall of Qam-Chee. Given the context of the quotes, it seems that Lukara has accepted that their fate is to die that day, and is putting a positive spin on it; she has accepted death, but not defeat.
To quote another Klingon proverb, it is "better to die on our feet than to live on our knees".
This sentiment is also echoed in the episode DS9: "Blood Oath":
Kang: "We will overwhelm the defenders and fight on to a glorious victory."
Koloth: "Or to a glorious death."
Kor: "It is a good day to die."
Again, the warriors appear to be accepting the possibility of death, but don't let that dissuade them.
A third example is in Star Trek: First Contact, where Worf is captaining the USS Defiant:
Worf: "Report!"
Officer: "Main power's offline! We've lost shields, and our weapons are gone!"
Worf: "Perhaps today is a good day to die! PREPARE FOR RAMMING SPEED!"
In this instance, Worf has not only accepted death; he is planning a kamikaze attack.
In all of these instances, the speaker appears to be speaking of their own death. However, there are also cases where this is left up to interpretation. For example, in TNG: "Sins of the Father", we have the exchange:
Duras: "You are a fool. Your challenge will only result in a fool's death."
Worf: "It is a good day to die, Duras ... and the day is not yet over."
This appears to be a threat, but it might also be taken as a simple statement: One of the two will inevitably die, and Worf has accepted both outcomes. He will either win, or die for a righteous cause. He will later use the phrase to echo a similar sentiment while brawling against Jem'Hadar in DS9: "In Purgatory's Shadow".
In DS9: "The Way of the Warrior", Sisko is trying to convince Gowron to call back his troops, stating that they are no match for DS9's defenses. Martok, meanwhile, is insisting that the DS9 is fooling their sensors to exaggerate their own capacity.
Sisko: "It is no illusion."
Gowron: "We shall see... Heghchu' jajvam jaj QaQ!"
Worf: "He said, 'Today is a good day to die.'"
Like Worf before him, Gowron appears to be entertaining two outcomes, but in either case, whoever dies shall die well.
While the exact background is never explained in canon, it is described thusly in the licensed book The Klingon Way by Marc Okrand:
Heghlu'meH QaQ jajvam.
This is an extremely common Klingon locution, often uttered when the odds seem to favor an opponent. It does not, however, represent a defeatist attitude. Quite the contrary, in a society in which warriors are so revered, to die in battle in a noble aspiration.
It is worth noting that in the "original" Klingon, the word Heghlu'meH can be translated as "for one to die", or "for anybody to die", with -lu' being the indefinite subject marker. In other words, it is explicitly neutral with respect to whose death the day favors.